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

Peek into the ORA-01555 reasons:

The ORA-01555 error can occur when a long read only transaction is run against database 

and there are many DML transactions being executed on database (on same data). 

The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle "Read Consistency Mechanism".

Oracle provides read consistency by reading the "before image" of updated data 

from "Online UNDO Segments". If there are lots of updates, long running read-only SQL 

and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:

1. Database should be in Automatic Undo Management mode.

2. Use a large optimal value for undo_retention.

3. Use a large optimal size of rollback segment (undo) size.

4. Should avoid to fetch (select / cursors) between commits.

5. Should Commit less often at the time of long running query, to reduce Undo Segment slot  reuse.

6. Try to run long running queries on off peak hours, when there is less DML transactions on 

database