ORA-01555

ORA-01555: snapshot too old: rollback segment number %n with name "%segname" too small.

What causes this error?

The rollback records needed by a reader for consistent read are overwritten by other writers.

This happens when your query starts at a given time, and as it runs, other people/tasks change the data to such an extent that Oracle cannot undo their changes. Remember, if your query started at time X, Oracle must give you the results of the data as it looked at time X (consistent read) and it cannot contain intermediate (inconsistent) changes.

How to fix it

Pick one or a combination of the following:

• Schedule your task when there are less database activity (maybe even ask people to stop working for a while).
• Optimize the query that is failing with this error to read less data and take less time
• Increase the size of the UNDO tablespace.
• Increase the size of the UNDO_RETENTION parameter.
• Set the UNDO tablespace in GUARANTEE mode.
• If you are exporting a table, consider exporting with the CONSISTENT=no parameter.
• Do not commit inside a cursor loop