he "ORA-01555 snapshot too old" error, which causes user transactions to fail, is a nightmare for Oracle DBAs and developers. It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.
It is hard to recreate this error and it occurs inconsistently: you may never encounter this error during the next run. The Oracle Server Error Messages manual gives the cause for this error as "Rollback segment too small." However, in most cases you'll find that your rollback segments are just fine; you have plenty of space in the rollback tablespace as well in the particular rollback segment that accommodates failed transactions.
So what is the problem and how do you solve it?
There are three situations that can cause the ORA-01555 error:
- An active database with an insufficient number of small-sized rollback segments
- A rollback segment corruption that prevents a consistent read requested by the query
- A fetch across commits while your cursor is open
The cause of the error involves a combination of the following factors: the setup of your rollback segments and the number and nature of the database concurrent transactions.
How do you avoid the ORA-01555 error, which is hard to recreate and crops up inconsistently?
From the DBA's perspective, you can avoid this error by allocating rollback segments and configuring them with the proper parameter values, as described.