ORA 30036

[logic]

Hi All,

I am getting the ora 30036 error and not sure how to take care of this error in datastage. The job runs fine when we run it stand alone. But the error.From what I think is that this error is due to running out of tablespace .Am I correct?. Should I request DBA to increase UNDO tablespace or is there any way to take care of this in dataStage itself?I am using the Clear table then insert rows in target table.
WOULD decreasing the transaction size be helpful?

Code: Select all

DELETE FROM SYSADM.EXTPS_NAMES 
SQLSTATE=HY000, DBMS.CODE=30036
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver][Oracle]ORA-30036: unable to extend segment by 64 in undo tablespace 'UNDO'

[ray.wurlod]

No.

There is no fix to this from DataStage apart from using smaller transactions.

Otherwise you have to negotiate with your DBA for a larger undo tablespace.

[logic]

Thanks Ray...I will try using smaller array size..

No.

There is no fix to this from DataStage apart from using smaller transactions.

Otherwise you have to negotiate with your DBA for a larger undo tablespace.

[chulett]

No, not a smaller array size - smaller transactions. Meaning commit more often during processing. When Transaction Size is set to zero, you only commit once when the job ends, something that requires the most amount of undo tablespace but makes restarting easy. Setting the Transaction Size to, say 1000, means commit after every 1000 rows have been sent to the target database. It keeps the amount of undo tablespace needed down to a minimum but seriously complicates any restart procedure.

[ray.wurlod]

What Craig said is 100% accurate and what my earlier post intended to convey.
Array size is only about how many rows at a time are sent to Oracle; this has nothing at all to do with undo table space.