snapshot too old ORA-01555 - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: snapshot too old ORA-01555

  1. #11
    Join Date
    Nov 2000
    Posts
    212
    there is excellent site [url]http://www.ixora.com.au/home.htm[/url] with really genious solutions for ORA-01555.

    One of solutions is to have a process to put uncommited transaction into EVERY rollback segment: that prevents oracle from rolling onto old rollback entries required by a query.

    It makes sense to look close to erro message: SNAPSHOT TOO OLD. It really means the most ugly reason for this error: no one can predict how long to keep old undo records.


  2. #12
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    I had this problem before. Increase dimension for OPTIMAL for your rollback segment, this should solve your problem.
    A long running query in one session(e.g rollback 1) that starts before a change on data is commited by another session (rollback 2) will see the data as it existed pre_commit. But the session that uses rollback 2 thinks that the data in commited and when it reaches the OPTIMAL size for rollback 2 it tries to cyrcle(doesn't go up to maximum size because thinks that the data at the begining of the rollback is commited). But this is not possible because if so then session on rollback 1 won't have a consisted view of changed data. That's why you get the "Snapshot too old' message. So you have to increase OPTIMAL size, because if you just increase maximum size of rbs it won't help.

  3. #13
    Join Date
    Oct 2000
    Posts
    449
    The site ixora.com looks to be real helpful for beginners..
    Thanks LND

  4. #14
    Join Date
    Nov 2000
    Posts
    212
    only for beginners?

  5. #15
    Join Date
    Feb 2001
    Posts
    4
    Hi,
    With all your feedback when i checked the time of abend's It look's like there is another program which is running and user's are doing updates on the table which i am using in my cursor . When my cursor takes snapshot of the data from one table , some other program alter the data in the snapshot , then that transaction is commited by the other program , then it might error out with ora 1555 . At this point i am planning to make my program run in a different timing at which there is no chance of the data which i am fetching is not going to be altered by any other program . As soon as i do that i will monitor it for some time , hopefully it should not abend . After that i will inform you all whether ora 1555 error is elimainated or not . Thank you all for your info on my prroblem , keep sending the info you have on this error , it might help us when we get this error in a different situation .

    Thanks
    Raghu

  6. #16
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    Have you increased OPTIMAL size of rollback segs? Trust me, it works

  7. #17
    Join Date
    Feb 2001
    Posts
    2
    This usually happens when you are trying to read rollback segments from another process which will disappear when the other process commits. I've seen this happen if the process takes too long (hours) in a system that has other update processes running at the same time. I suggest tuning the process to run faster or running it when the system is less busy.

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