ORA-01555 Snapshot Too Old: Developer and DBA Notes

by

ORA-01555: snapshot too old usually means Oracle could not reconstruct the older version of data needed by a query because the required undo information was no longer available.

Why it happens

  • A long-running query needs older row versions while other sessions keep changing the same data.
  • The undo tablespace is too small for the workload.
  • Undo retention is lower than the time needed by important queries.
  • Large batch commits or heavy DML increase pressure on undo.

Useful checks

Check the failing SQL, query duration, undo tablespace size and current undo retention. Views such as V$UNDOSTAT help show undo usage and tuned retention over time.

SELECT begin_time, end_time, tuned_undoretention, ssolderrcnt
FROM   v$undostat
ORDER  BY begin_time DESC;

Typical fixes

  • Size the undo tablespace for the real workload.
  • Increase retention when long-running queries genuinely need it.
  • Reduce unnecessary query duration through SQL tuning.
  • Avoid application patterns that keep cursors open while repeatedly modifying the same data set.

The right fix depends on whether the pressure comes from SQL performance, undo sizing, or application behaviour; simply increasing retention is not always enough.

Leave a Comment