ORA-01555: Snapshot too old
ORA-01555: Snapshot too old: rollback segment number XX with
name NNN too small.
There are various reasons why users get the
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).
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.
About the author: Brian Cryer
is a dedicated software developer and webmaster. For his day job he
develops websites and desktop applications as well as providing
IT services. He moonlights as a technical author and consultant.