there is excellent site http://www.ixora.com.au/home.htm 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.
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.
The site ixora.com looks to be real helpful for beginners..
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 .
Have you increased OPTIMAL size of rollback segs? Trust me, it works
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.