DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Undo and snapshort too old

  1. #1
    Join Date
    Nov 2000
    Posts
    169

    Undo and snapshort too old

    How do you fix snapshot too old when using UNDO's in 9.2.0

    ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$"
    too small

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You can extend the datafile for your undo tablespace.
    What SQL were you running when you got the error message?
    Normally when someone is performing a long running query
    on a table that is changing, eventually Oracle not be able to
    maintain a read consistant view of the underlying table.

    If you change your code you might also be able to eliminate the
    snapshot too old error message.

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    First of all, you need to identify the longes running query.
    Second, you need to set the undo_retention initialization parameter to that time (say your longest running query is 2 hours, set undo_retention=7200).
    Third, allocate enough space un the undo tablespace.

    Cheers,
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by gandolf989

    If you change your code you might also be able to eliminate the
    snapshot too old error message.
    Like having a commit every # of records.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by reydp
    Like having a commit every # of records.
    that's worse

  6. #6
    Join Date
    Feb 2001
    Posts
    295
    Another classic reason for ORA-01555 is to leave cursors opened and fetch across commits. Depending on the query, it doesn't even need to be so "long-running"
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by adrianomp
    Another classic reason for ORA-01555 is to leave cursors opened and fetch across commits. Depending on the query, it doesn't even need to be so "long-running"
    Of course most cursors should be implicit cursors, removing the necessity to close cursors in code, as they will be closed for you. But certainly, fetching accross commits with open cursors will cause the ORA-01555.

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