ORA-01555: Snapshot too old (Oracle)

The Oracle 'ORA-01555: snapshot too old' error indicates that the old (undo) data a query needs for a consistent read has already been overwritten. This tree narrows down the source of the error (long query + insufficient UNDO, high undo turnover, or LOB) and leads you to the right fix.

Where does the error occur?

First determine the context of the error. Does the classic ORA-01555 occur during a long-running SELECT/report, or while accessing a LOB (CLOB/BLOB) column? LOB-related ORA-01555 is a different mechanism (PCTVERSION/RETENTION) and is resolved separately.

SELECT sql_id, elapsed_time/1e6 AS sec, sql_text
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

• During a long-running query/report
• While accessing a LOB column

Is UNDO sufficient, is retention appropriate?

A long query reads the old image of blocks changed since it started from UNDO. If the UNDO tablespace is small or undo_retention is shorter than the query duration, old undo is overwritten. Measure UNDO usage and retention.

SHOW PARAMETER undo_retention;
SELECT tablespace_name, SUM(bytes)/1024/1024 AS mb, status
FROM dba_undo_extents GROUP BY tablespace_name, status;
SELECT MAX(maxquerylen) AS longest_query_sec, MAX(tuned_undoretention) AS tuned_retention
FROM v$undostat;

• UNDO is small / retention shorter than the query
• UNDO is sufficient but commits are very frequent
• The query runs excessively long

Root cause: insufficient UNDO / retention

The old undo needed by the long query was overwritten because the retention period expired or the tablespace filled up. Grow UNDO and set retention to cover the query duration.

Root cause: high undo turnover (frequent commits)

Heavy DML with very frequent commits recycles undo blocks quickly and wipes out the old image the long query needs. The 'commit cleanout' effect makes this worse.

Root cause: excessively long query duration

The query runs so long that even a reasonable undo_retention is not enough. The permanent fix is to speed up the query or offload its workload.

Root cause: LOB retention / PCTVERSION

ORA-01555 while reading a LOB indicates that the space holding old LOB versions (RETENTION or PCTVERSION) is insufficient. LOBs have a separate versioning mechanism.