warning when loading database

arun@sinmax · Post by **arun@sinmax** » Mon Feb 06, 2006 6:42 am

Hi all
when i try to load 1000000 rows into oracle database through DRDBMS stage i am getting the following warning. and also only 44000 rows alone got inserted into the table.
TGT_CTY,0: Warning: TGT_CTY: ORA-01562: failed to extend rollback segment number 5
ORA-01562: failed to extend rollback segment number 5

kindly help me

regards
arun

ArndW · Post by **ArndW** » Mon Feb 06, 2006 6:48 am

Arun,

this is a common error when your database is not set up for the type and size of job you are running. You should talk to your DBA about the rollback space set up for Oracle.

A quick workaround is to increase your commit frequency in your DataStage job; but if you feel that it is set to good size then your DBA will need to allocate more space.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 06, 2006 1:45 pm

You are trying to force Oracle to "remember" too many before-images by creating such a large transaction. Your Oracle DBA will explain what caused this error. As Arnd posted, either use smaller transactions or larger/more rollback segments. Search the forum; this has happened to others in the past.

aramachandra · Post by **aramachandra** » Tue Feb 07, 2006 10:00 am

Please boot me if this is a hijacking of the topic... but on a related to note, i would like to clarify that if

In using the oracle stages in parellel jobs, if
one is using the LOAD TRUNCATE method, i.e any of the LOAD methods, there is no way to specify the commit size... , The $APT environment variables are specific to upserts...

So you have no choice but to request yourDBA to increase the rb segment or to limit your data size....

Any thoughts on if there is a way to specify the commit size when using the LOAD TRUNCATE/REPLACE methods?

Thanks
Arvind