ora 1555 snapshot too old
518838Dec 4 2006 — edited Dec 13 2006Hi,
I'm getting the Oracle error
"ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small"
The application which is having this problem, use to have the error on frequent basis(in 15-20 days), but the place of error is not fixed. The error comes at any place randomly.
What I found, there can be below causes for this error:
-> Size of rollback segment is not enough
-> Data changed & committed between execution of the query finishes.
-> Frequent commits in between application cursors
-> Rollback segment corrupted
While we asked DBA to increase the size of rollback segments, he came back with below workaround:
Quote:
What really happens is that in a PL/SQL loop data is committed inside of the loop.
The loop data itself (like the loop counter) is stored in rollback, what happens is that after a commit everything is marked as available.
Further updates can then overwrite rollback data, including the loop counter.
As soon as the loop counter is destroyed while it is still inside the loop Oracle will throw an ora-1555 error.
The only workarounds for this problem are:
- commit less often or do not commit at all inside PL/SQL loops
- make sure the process finishes within the undo_retention_time so that data is not overwritten
Unquote
Now the problem is how can we verify that increase in size of rollback segment will solve the problem to a great extent, or how can we verify the main culprit of the error.
As error is coming randomly at different places, code review will not work
Is there any other solution through the trace files, or any other workaround.
Deepak