-
Concerned about rollback segment
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.
-
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.
-- Dilip
-
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.
OCP 8i, 9i DBA
Brisbane Australia
-
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.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
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.
Last edited by patel_dil; 12-02-2002 at 01:24 PM.
-- Dilip
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|