ORA-01555: snapshot too old

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Details:

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

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 segment. 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.

So users may potentially experience this error whenever a long-running query is executed at the same time as data manipulation(DML) transactions.

How to avoid/fix this error :

• Increase the size of all the roll back segments.
• Add more rollback segments.
• Increase the ‘OPTIMAL’ size of the roll back segments.
• Tune the application to “commit” more frequently such that smaller rollback space is used and this condition can be avoided.
• Oracle recommended ‘proper’ solution is to schedule long-running queries at times when online transaction processing is at a minimum.

Example:

ORA-01555 Error Scenario:

Sample application logic causing ORA-01555 error:

Prepare a select
fetchrowArray
while the row is not null
update the row
if nrows mod commitsize then commit
fetch the next row
end while

final commit

This application is developed in Perl, DBD, DBI and oracle. It raised the error as shown below:

DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small (DBD ERROR: OCIStmtFetch) [for Statement "

Analysis and Resolution:

As you can see from the application logic, the rows selected by the outer select query are being modified by the update statement. ie both are acting on the same table . It can be resolved by setting UNDO_RETENTION appropriately large enough BUT it may not be the best solution.
The better solution may be separating out select and updates in to different transactions.

After rewriting the application logic by separating data selection and data update activities, this “ORA-01555: snapshot too old” no longer occurred.