Concerned about rollback segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Concerned about rollback segment

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    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.

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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

  5. #5
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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 12: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
  •  



Click Here to Expand Forum to Full Width