UNDO TABLESPACE

karry450 · Post by **karry450** » Tue May 25, 2010 10:50 pm

Hi Friends,

Im loading flat file to Target table oracle, I am getting this error can anyone help me please.

ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDO1'

and the job gets aborted.

chulett · Post by **chulett** » Tue May 25, 2010 11:05 pm

Talk to your DBA. They may need to increase that tablespace or you may need to commit more often. Or perhaps just wait and try again.

karry450 · Post by **karry450** » Tue May 25, 2010 11:20 pm

chulett wrote:Talk to your DBA. They may need to increase that tablespace or you may need to commit more often. Or perhaps just wait and try again. ...

Thanks Chulett, I have even asked my DBA to increase the table space but that didnt work work out still getting the same error .

And I have tried running it lot of times.

Any other alternate would help me a lot.

Thanks

chulett · Post by **chulett** » Tue May 25, 2010 11:23 pm

Then they didn't increase it enough.

How big is your flat file? What kind of "load" are you doing?

karry450 · Post by **karry450** » Wed May 26, 2010 12:33 am

chulett wrote:Then they didn't increase it enough.

How big is your flat file? What kind of "load" are you doing? ...

Flat file size is around 4678712 bytes, Im just loading into target(straight Load) with a transformer stage in between flat file and target(oracle OCI stage version 2) written a custom INSERT query in the target which also consists of SEQUENCE.next val for generating sequence numbers for one field.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed May 26, 2010 1:56 am

What is your commit size ?

karry450 · Post by **karry450** » Wed May 26, 2010 2:50 am

Sainath.Srinivasan wrote:What is your commit size ?

Array size 50
Transaction Size 1000
rows per transaction 1000

Please let me know If i have change or increase anything

ray.wurlod · Post by **ray.wurlod** » Wed May 26, 2010 3:57 am

Decrease transaction size (rows per transaction) to, say, 50. If the UNDO tablespace is OK after that, try increasing by a factor of 2 until the problem recurs, then back off till it disappears again.

karry450 · Post by **karry450** » Wed May 26, 2010 5:45 am

ray.wurlod wrote:Decrease transaction size (rows per transaction) to, say, 50. If the UNDO tablespace is OK after that, try increasing by a factor of 2 until the problem recurs, then back off till it disappears again ...

Thanks Ray , It worked at 100 transaction size

chulett · Post by **chulett** » Wed May 26, 2010 6:11 am

They shouldn't be so stingy with their undo space.

Please mark your post as Resolved using the button at the top of the page.