ORA-01555: snapshot too old

ORA-01555: snapshot too old: rollback segment number N with name "_SYSSMU…$" too small

A long-running query needed an older version of some data to stay read-consistent, but that undo had already been overwritten. Make the query faster, keep undo longer (UNDO_RETENTION and a large enough undo tablespace), and avoid committing inside a loop over an open cursor.

What it means

Oracle gives every query a consistent view of the data as of its start time. When rows change during the query, Oracle rebuilds their old versions from undo. If the query runs longer than the undo is kept — or the undo space is reused because it is too small — the old version is gone and the query fails with ORA-01555, sometimes after running for hours.

Common causes

• A query or report that runs longer than UNDO_RETENTION.
• An undo tablespace too small to keep undo for that long under the current change rate.
• Fetching across commits: a loop over an open cursor that commits on each iteration.
• Heavy concurrent DML on the tables the long query reads.

How to fix it

1. Tune the long query first — the shorter it runs, the less undo it needs.
2. Check V$UNDOSTAT (MAXQUERYLEN, TUNED_UNDORETENTION) and raise UNDO_RETENTION above your longest query.
3. Make sure the undo tablespace can hold that much undo (larger datafiles or autoextend).
4. Do not commit inside a cursor loop; commit once at the end or process in independent batches.
5. For critical jobs, a DBA can set RETENTION GUARANTEE on the undo tablespace — at the risk of DML failing instead.

Example

How long are queries running versus undo retention?

SELECT MAX(maxquerylen)          AS longest_query_s,
       MAX(tuned_undoretention)   AS tuned_retention_s
  FROM v$undostat;

SHOW PARAMETER undo_retention

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-00060ORA-00054ORA-01403 All Oracle errors →

Oracle documentation for ORA-01555. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.