ora-1555 error with oracle enterprise stage

kirankota79 · Post by **kirankota79** » Fri May 04, 2007 10:14 am

Ho can i can overcome the ora-155 error while updating the database. should i reduce the number of rows to be comitted after the update? please help. Is it possible to do from datastage side, or it should be done on the database level?

thanks in advance

csrazdan · Post by **csrazdan** » Fri May 04, 2007 10:57 am

ORA-155 is snapshot too old error. You can either reduce the transaction size and increase the size of rollback segment.

On DataStage Side - Add an environment variable APT_ORAUPSERT_COMMIT_ROW_INTERVAL and set a value for this parameter. The default value is 5000

On Database side - Contact your DBA and ask him/her to increase the size of Rollback Segment. If you have multiple rollback segments I'm not sure if DataStage has an option of using the largest one. In good old PL/SQL days you could assign a rollback segment to transaction. You may have to do some research on this.

Hope it helps...

kirankota79 · Post by **kirankota79** » Fri May 04, 2007 11:26 am

the value is already set to 5000. will it be helpful if i set it to 1000.

csrazdan · Post by **csrazdan** » Fri May 04, 2007 12:27 pm

5000 is the default value. Since you have to reduce the transaction size 1000 should be a good number to try with. If job fails again try with even smaller number.

kirankota79 · Post by **kirankota79** » Fri May 04, 2007 12:39 pm

at the database level the, for every 10000 rows it will make a commit and in datastage i set it to 5000. Which one it will consider?

csrazdan · Post by **csrazdan** » Fri May 04, 2007 12:43 pm

Whatever you set it in job that is what it will consider because commit command will be issued.

kirankota79 · Post by **kirankota79** » Sun May 06, 2007 4:15 pm

i set the value of the row commit interval to just 100...even then iam getting the ora-1555 error. This problem was solved for the tables which has records of about 100,000, but iam getting this error when the table consists of the records more than 1 million. My dba told there is no way to increase the size of the rollback segement since they dont have the privilege to do it. what ever solution is i have to do from datastage side. plase suggest me a feasible solution for it.

chulett · Post by **chulett** » Sun May 06, 2007 5:23 pm

kirankota79 wrote:My dba told there is no way to increase the size of the rollback segement since they dont have the privilege to do it.

What? Talk to whatever 'DBA' you've got that does have the privs.

Another option is to run your processes when the tables you are updating aren't quite so busy. A 'snapshot' error like that implies usage by others and Oracle doesn't have the resources it needs to hold a steady picture of the state of that data it needs over the course of your run. So you either need to increase the resources available or reduce their usage.

Found this article which might help. Unfortunately, the first linked article at the bottom no longer seems to exist and I couldn't find it elsewhere. The second is good reading and some of the advice can be put to use by you but most is geared to a DBA, or at least one with the privileges needed to actually make changes to the database.