Concerned about rollback segment

[pcotten]

We have long running job. It selects and inserts. It has been running too long. A few months ago it ran for over 12 hours and errored out with ORA-1555 snapshot toll old error. I know which rollback segments this job is using.

Is there any way to check rollback segments to see if we might get this error? Thanks.

[patel_dil]

For long running job, it is better to assign a single large rollback segment exclusively by using SET TRANSACTION USE ROLLBACK SEGMENT big_rollback_segment_name. Make sure you *online* it before using, and *offline* after use. That will help avoid your 'snapshot' error. Hope that helps.

[grjohnson]

I see assigning a transaction to a particular segment as a temp fix these days. Cause if you want to use AUTO UNDO segments in 9i, you can't assign trasactions.

YOu need to determine WHY the you are receiveing the error. I.e segment to small, not enough rollback segments, optimal to small, commiting in a cursor, commiting too often and others issues.

There are numerous issues that can cause this ambiguous error, I prefer establising WHY it occurs as to instantly having a BIG segment (in this case it doesn't look like size is an issue)

Also, if you are SET to a rollback segment you have to take the ROLLBACK SEGMENT offline immediately (leaving the segmnet in an OFFLINE PENDING state), otherwise other transactions can utilise the RBS segment... and your advantage is instantly lost.

[Calvin_Qiu]

Originally posted by patel_dil
For long running job, it is better to assign a single large rollback segment exclusively by using SET TRANSACTION USE ROLLBACK SEGMENT big_rollback_segment_name. Make sure you *online* it before using, and *offline* after use. That will help avoid your 'snapshot' error. Hope that helps.

He was getting "Snapshot too old error" not "Running out of rollback segment", so your solution might not helpful to this case. Agree with grjohnson, try to find out the problem. In general, it might be a long query.

[patel_dil]

sorry my bad. I think I replied too fast. But ORA-01555 is also caused due to rollback segment being overwritten and oracle is unable to get old version of blocks in that segment. In that case, assigning a Big Rollback segment should help. And I agree too with grjohnson's views in regards to Oracle 9i.

Thanks.