Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: ORA-01555: snapshot too old - URGENT

    hi all,

    My oracle version is 8.1.7.4 on AIX

    ORA-01555: snapshot too old: rollback segment number 9 with name "RBS7" too small.
    I am trying to import nearly 1 million rows into oracle using a procedure i got the error message ora-01555.the optimal size for rollback segments 50m there nearly 8 rollback segments, then i further added two rollback segments which did not fix the issue.
    the application which does the import is coded to issue commit for every 300 rows then i went ahead and increased the tablespace size to 1000m which was earlier set to 800m now the optimal size for each segments have changed but still after 60000 inserts it still the same error this time for different rollback segment


    Could you kindly help me with this issue

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Try shrinking all your rollbacks as this may help and or increase the size of the tablespace.

    Alan

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Make one LARGE rollback segment and use an alter session to force your update to use it. An individual transaction (everything before a commit) will never use more then one rollback segment. Also, get rid of your commit during the update. Many people are under the misconception that issuing commit during a mass update will decrease ora-01555's when the exact opposite is true, it will cause it to happen more often.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the import is coded to issue commit for every 300 rows
    Only COMMIT once at the end
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Can you tell us how are you "importing" this data ? It is not clear to me how are you "importing" data and commiting every 300 rows (unless you're under 'row-by-row' processing). Perhaps we can suggest you other methods.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    beilstwh, very interesting statement you made when you said "An individual transaction (everything before a commit) will never use more then one rollback segment.". At first It shocked me as to transactions boundaries limited by how you size your rollbacks, but then doing some test I encountered that when a transaction cannot more fill undo for the changes you made (inserts mostly), you get a ORA-01562. Even if you tried to use another rollback (on the same transaction), it will complain with ORA-01453.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Yep, I found out the hard way that you have to size your rollback segs to be big enough for any concevable transaction. What many sites do is to make one very large rollback seg for use by large transactions and specifically select it for those jobs. That way, you don't have to have all of them sized very large.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Actually, there's still the probability of getting ORA-01555 even when assigning your transaction to a very big rollback segment.

    Suppose you have one big rollback segment of, say, 100mb called RBSBIG and another tiny rollback segment of, say, 1mb called RBSTINY. You have your long running transaction, which have to revisit blocks allocated to table T. Say you start this transaction at 1PM on session A. By 1:01 PM session B updates some rows on table T, and this transaction is assigned to rollback segment RBSTINY. Session B commits. At 1:02 PM session A has to re-visit the blocks that point to the updated rows that session b modified on the buffer cache that belongs to table T and encounters they are being modified, it then visit the rollback segment RBSTINY (since on these blocks' header the rollback segment assigned to the transaction was RBSTINY), it cause a delayed block cleanout, but it would read undo data as of the point in time the transaction started (1 PM) from rollback segment. At 1:02 PM now session C comes along and makes an update (this transaction is also assigned RBSTINY) making RBSTINY to wrap around, and commits. Now at 1:03 PM session A again has to revisit the same blocks on the buffer cache, that belong to table T. It will hit ORA-01555 since the undo data kept there is AFTER the point in time its transaction started.

    What I always like to do is to have them uniformilly start small in size (without assigning an optimal size for them) of, say, 10mb. Then I begin adding space up until I hit no more ora 01555.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by wijisidd
    the application which does the import is coded to issue commit for every 300 rows
    That is what usually causes ORA-01555. Each time you commit and start a new transaction, the database has to assign you a different rollback (undo) segment, which it does by cycling through the list of available undo segments. By processing 1 million rows and committing every 300, you are calling for this switch 3333 times. At some point in this you are quite likely to revisit and overwrite some undo information you still needed for read consistency.

    The part of the error message about the rollback segment being too small is rather misleading IMO, and the inference a lot of developers make is that they must be using up too much undo space at a time (not quite the point) and that they should therefore release it periodically (which doesn't help - you still need it).

    From 9i onwards UNDO_RETENTION may help.
    Last edited by WilliamR; 10-20-06 at 06:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •