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
ora-1555 error with oracle enterprise stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
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...
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...
Assume everything I say or do is positive
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 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.
What? Talk to whatever 'DBA' you've got that does have the privs.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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
![Idea :idea:](./images/smilies/icon_idea.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers