Cry about...
Oracle Troubleshooting

ORA-01555: Snapshot too old

Symptom:

The error:

ORA-01555: Snapshot too old: rollback segment number XX with name NNN too small.

Cause:

There are various reasons why users get the ORA-01555 error. The most common reason is that the rollback segments are too small.

Background: Oracle uses rollback segments to reconstruct the read-consistent snapshot of the data. Whenever a transaction makes any changes, a snapshot of the record before the changes were made is copied to a rollback. Once a transaction is complete, its data is not deleted from the rollback segment. It remains there to service the queries and transactions that began executing before it was committed. This may cause problems with long queries because these blocks may be overwritten by other transactions, even though the separate long-running query against those blocks has not completed.

Accordingly, this error can arise when a long-running query is being executed at the same time as data manipulation transactions.

See also ‘Oracle Troubleshooting’ (book reference to be added).

Possibly Remedies:

• Increase the size of all the roll back segments.
• Add more rollback segments.
• Increase the ‘OPTIMAL’ size of the roll back segments, i.e.:

  Alter rollback segment "XXX" storage (optimal NNNK);

• If the error seems to be associated with a particular application (job or script and not necessarily the one producing the error) then investigate whether that application’s use of rollback space could be reduced. For example, consider whether the application could 'commit' more frequently. For long running applications (particularly if it is the application that is encountering the error) consider whether any record sets could be refreshed periodically rather than relying upon the same record set contents for the entire run of the application.

These remedies reduce the likelihood of the error arising. Oracle points out that this is not a true solution, but a delaying tactic since the rollback segment may eventually overwrite all of its data blocks. The Oracle recommended ‘proper’ solution is to schedule long-running queries at times when online transaction processing is at a minimum.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.