Oracle error
ORA-01555
ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small
What usually causes it
A long query needed undo to rebuild an older version of a block, and that undo had already been overwritten.
What to check first
Size the undo tablespace and UNDO_RETENTION for your longest query, and tune that query. Avoid committing inside a loop that's still fetching from the same table.
Scripts that help
- Undo usage and ORA-01555 riskUndo extents by status, then the longest query and any snapshot-too-old or out-of-space errors from V$UNDOSTAT (roughly the last four days).
- Open transactions and the undo they holdEvery open transaction with its session, undo size and start time. An old transaction from an idle session is usually someone who forgot to commit.
- SQL active for 30 minutes or more (all instances)Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and…