oracle 'ORA-01555: snapshot too old' on a long report query: how to fix

TL;DR

Give the query's read-consistent image more undo to live in. Raise UNDO_RETENTION so undo data survives longer than your longest report, and make sure the undo tablespace is big enough to hold it. ORA-01555 means concurrent DML overwrote the old row versions your long query still needed.

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_123456$" too small

Steps

1. Compare query runtime to undo retention: query v$undostat for MAXQUERYLEN and TUNED_UNDORETENTION. Expected: max query length exceeds the tuned retention; that is the mismatch.
2. Check the undo tablespace has room: query the tablespace usage metrics for UNDO. If it is full, raising retention alone will not stick. Expected: you know whether space or the setting is the limit.
3. Raise UNDO_RETENTION to comfortably exceed your longest report (for example double the report runtime), and grow the undo tablespace if needed. Consider guaranteed undo retention if the reports are business-critical. Expected: retention now exceeds max query length.
4. Reduce concurrent DML pressure during the report window if you can; fewer overwrites means less undo churn. Expected: less undo generated per hour in v$undostat.
5. Re-run the report. Expected: it completes without ORA-01555.

Use this when

• Long Oracle reports fail with ORA-01555
• The error appears under concurrent write load
• Nightly reports fail but quick queries work fine
• The failure started after write volume grew

Not for this skill when

• ORA-01555 hits short queries (that is usually a tiny undo tablespace or a block-cleanout issue, a different fix)
• You see "snapshot too old" in other databases (Postgres handles this with vacuum tuning, not undo retention)
• The report is slow but does not error

Variant phrasings

• ora-01555 snapshot too old how to fix
• oracle undo_retention snapshot too old
• long running query ora-01555
• oracle report fails snapshot too old

Why it happens

Oracle gives every query a read-consistent view built from undo data. Long queries need old undo; meanwhile concurrent transactions keep overwriting rows and the database eventually reuses the undo space. When the query reaches for a row version that has been overwritten, you get ORA-01555.

Edge cases

• Guaranteed retention can stall DML if the undo tablespace fills; monitor space, not just the setting.
• Commits in tight loops generate the most undo churn; batch them outside report windows.
• LOB columns have their own retention on the LOB storage; the undo setting does not cover them.
• Fetching across a database link makes the query longer than it looks; the remote side's undo matters too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YkO0pvc0MDfka2WX25UaDw

Resolved from the public thread: https://vectle.com/posts/pst_YkO0pvc0MDfka2WX25UaDw

Thread query: oracle 'ORA-01555: snapshot too old' on a long report query: how to fix

TL;DR: Give the query's read-consistent image more undo to live in. Raise UNDO_RETENTION so undo data survives longer than your longest report, and make sure the undo tablespace is big enough to hold it. ORA-01555 means concurrent DML overwrote the old row versions your long query still needed.