What causes ORA-01555 "snapshot too old", and how do I fix it?

A report that has run for 45 minutes dies with:

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_..." too small

Or an overnight expdp export fails at 95%. The message blames a "rollback segment", but rollback segments haven't been managed by hand for 20 years. To fix it you need to know how Oracle gives queries a consistent view of the data.

Read consistency is built from undo

Oracle guarantees that a query sees data exactly as it was when the query started, even while other sessions change and commit rows underneath it.

When a block has changed since your query began, Oracle doesn't wait or lock. It rebuilds the old version of the block by applying the undo records that the changing transaction wrote. Undo lives in the undo tablespace, and after a commit that undo is only kept while space and UNDO_RETENTION allow.

ORA-01555 means: your query needed an old version of a block, and the undo required to rebuild it has already been overwritten. The longer a query runs and the more DML happens at the same time, the more likely this becomes.

The usual causes

1. Undo retention is too short for your longest query. UNDO_RETENTION defaults to 900 seconds (15 minutes). With auto-extending datafiles Oracle tunes it upward on its own, but with a fixed-size undo tablespace it can only keep what fits.

2. The undo tablespace is too small. Retention is best effort: when space runs out, Oracle overwrites expired, and eventually unexpired, undo so that current transactions can keep going.

3. Committing inside a loop that reads the same table ("fetch across commit"):

   FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP
       UPDATE orders SET status = 'DONE' WHERE id = r.id;
       COMMIT;                    -- releases the undo the cursor still needs
   END LOOP;
   

   The cursor's snapshot is from before the loop, and every COMMIT makes the undo it depends on eligible for reuse. The job is causing its own ORA-01555.

4. Delayed block cleanout after a huge batch update, where later readers have to look up old transaction status in undo that's already gone.

Diagnose with V$UNDOSTAT

SELECT begin_time, end_time,
       maxquerylen,          -- longest query (seconds) in the interval
       tuned_undoretention,  -- retention Oracle actually achieved
       ssolderrcnt,          -- ORA-01555 count
       nospaceerrcnt         -- "out of undo space" count
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 48 ROWS ONLY;

If maxquerylen is regularly bigger than tuned_undoretention, your longest queries are outliving their undo. The Undo Advisor (in OEM, or DBMS_UNDO_ADV) estimates the tablespace size you need for a target retention.

Fixes

1. Size undo for the longest query plus a margin.

ALTER SYSTEM SET undo_retention = 7200 SCOPE = BOTH;   -- 2 hours

ALTER DATABASE DATAFILE '/u02/oradata/PROD/undotbs01.dbf'
  AUTOEXTEND ON NEXT 1G MAXSIZE 64G;

2. Guarantee retention during critical windows (use with care):

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

With a guarantee, Oracle won't overwrite unexpired undo. If undo fills up, DML fails with ORA-30036 instead of queries failing with ORA-01555. That's a better trade for a nightly export and a worse one for an OLTP system.

3. Remove fetch-across-commit. Do the work in one set-based statement, or commit in batches driven by a key that doesn't depend on a long-running cursor:

UPDATE orders SET status = 'DONE' WHERE status = 'NEW';
COMMIT;

For huge volumes, process key ranges (WHERE id BETWEEN :lo AND :hi) with a fresh query for each batch.

4. Make the long query shorter. A missing index that turns a 5-minute report into a 60-minute full scan multiplies the ORA-01555 risk by 12. Tune the query before you buy more undo.

5. Schedule around heavy DML. Exports and big reports that run during batch loads are the classic collision.

6. For Data Pump exports, FLASHBACK_TIME=SYSTIMESTAMP gives a consistent export, which also depends on undo. Size retention for the whole export.

Checklist

1. ORA-01555 means the undo needed for read consistency was overwritten.
2. Compare maxquerylen with tuned_undoretention in V$UNDOSTAT.
3. Increase UNDO_RETENTION and make the undo tablespace able to honor it.
4. Never commit inside a loop over a cursor on the same table.
5. Shorten the long query itself.