ORA-01555 Snapshot Too Old — Undo Retention Fix

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12" too small. It kills the long ones — the 6-hour analytics rollup, the full-export select, the month-end reconciliation that scans everything. Short queries never see it; marathon readers meet it at 3 AM when the report dies at 94% complete after hours of work.

Oracle promises every query a consistent snapshot as of its start (SCN), reconstructed from undo. That promise holds only while undo survives: concurrent writers overwrite old versions as soon as they pass UNDO_RETENTION, and a query still reading them fails loudly instead of returning wrong data.

This guide covers both sides of the race: sizing retention and undo storage from v$undostat, ending fetch-loop commits, calming hot-block churn, doing the retention math for your longest query, and redesigning reads that no retention setting can cover. Every section pairs the storage fix with the design fix, because error 01555 always has one foot in each world.

Read Consistency in 90 Seconds

Every Oracle query sees the database as of one instant — its start SCN. Blocks changed after that instant get rolled back in memory using undo, so the query observes a frozen past while writers march on. This is read consistency: no locks on readers, no dirty reads, results that make sense as of a moment. Undo is the time machine, and like all time machines it has fuel limits.

The fuel gauge has three dials. UNDO_RETENTION (seconds, default 900) is the requested history depth — how long expired undo should survive before reuse. The undo tablespace size plus autoextend is the physical room: retention is a wish without space to honor it. RETENTION GUARANTEE (per tablespace) turns the wish into a promise — at the cost of failing writers (ORA-30036) when space runs out instead of recycling readers' history.

Check all three before theorizing: SHOW PARAMETER undo_retention, dba_tablespaces retention column, dba_data_files autoextend flags. Most 01555 scenes show the same tableau — 900 seconds, fixed-size files, no guarantee — against queries measured in hours. The gap between the dials and the workload is the entire diagnosis. Write the three values into the ticket before theorizing further.

-- The three dials: requested retention, guarantee, physical room
SHOW PARAMETER undo_retention;
SHOW PARAMETER undo_tablespace;
SELECT tablespace_name, retention FROM dba_tablespaces
WHERE contents = 'UNDO';
SELECT file_name, ROUND(bytes/1024/1024) AS mb,
  autoextensible, ROUND(maxbytes/1024/1024) AS max_mb
FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter
  WHERE name = 'undo_tablespace');

UNDO_RETENTION and the Undo Tablespace

UNDO_RETENTION is a target the space manager honors when room allows: expired undo older than retention gets reused only under space pressure, and auto-tuned retention (tuned_undoretention in v$undostat) stretches beyond your setting when space permits. The setting is therefore a floor for requests, not a promise — the promise comes from space plus RETENTION GUARANTEE. Raising retention without adding space is wishing louder, not fueling further.

Size both sides together. Set UNDO_RETENTION above your longest query plus margin (20%), enable AUTOEXTEND on undo datafiles with a sane MAXBYTES cap, and add datafiles until the tablespace holds retention-seconds × peak-undo-rate. For sacred reports, ALTER TABLESPACE ... RETENTION GUARANTEE converts recycling into writer backpressure — monitor for ORA-30036 afterward, because guarantee trades reader failures for writer failures when sizing is short.

Apply online and verify from history, not hope: ALTER SYSTEM SET undo_retention=21600 SCOPE=BOTH takes effect immediately, and v$undostat over the next cycle shows whether tuned retention actually reaches the workload's maxquerylen. If tuned retention flatlines below your setting, space — not configuration — is the binding constraint. Add room.

-- What retention does reality demand? (seconds)
SELECT MAX(maxquerylen) AS longest_query_s,
  MAX(tuned_undoretention) AS tuned_s
FROM v$undostat;

-- Raise the floor + grow the room (online)
ALTER SYSTEM SET undo_retention = 21600 SCOPE = BOTH;
ALTER DATABASE DATAFILE '/u01/oradata/undotbs01.dbf'
  AUTOEXTEND ON NEXT 1G MAXSIZE 120G;

-- Promise retention for sacred reports (watch for ORA-30036)
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Commits Inside Fetch Loops

The classic self-inflicted 01555: a PL/SQL loop that FETCHes a row, processes it, COMMITS, and repeats. Each COMMIT ends the transaction and releases the cursor's snapshot anchor; the next FETCH re-establishes position against data that moved on — and against undo that may already be gone. The loop manufactures the exact inconsistency it then dies from, usually reported as snapshot-too-old or fetch-out-of-sequence depending on timing.

Restructure to batch semantics: BULK COLLECT ... LIMIT 1000 into collections, FORALL the writes, one COMMIT per batch — fetching stays inside a stable snapshot per batch while commits happen between fetches, never inside them. When processing must interleave reads and writes row-by-row, split sessions: one session holds the read cursor untouched, a second performs the writes and commits. The read snapshot then survives the entire pass.

Lint loops for COMMIT placement in review: any COMMIT lexically inside a FETCH loop is a defect until proven otherwise. The pattern hides in exception handlers too — a COMMIT in the loop's exception branch fires exactly when the data is most turbulent. Batch it, split it, but never commit mid-cursor. Ever. No exception.

-- WRONG: commit inside the fetch loop (releases snapshot position)
-- LOOP FETCH c INTO r; EXIT WHEN c%NOTFOUND; process(r); COMMIT; END LOOP;

-- RIGHT: bulk fetch, forall write, commit per batch
DECLARE
  CURSOR c IS SELECT order_id, total FROM shop.orders WHERE processed = 'N';
  TYPE t IS TABLE OF c%ROWTYPE;
  rows_ t;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO rows_ LIMIT 1000;
    EXIT WHEN rows_.COUNT = 0;
    FORALL i IN 1..rows_.COUNT
      UPDATE shop.orders SET processed = 'Y'
      WHERE order_id = rows_(i).order_id;
    COMMIT;  -- between fetches, never inside one
  END LOOP;
  CLOSE c;
END;
/

Delayed Block Cleanout and Hot Blocks

Every committed change leaves its mark on data blocks until cleanout visits them; blocks bulk-loaded or mass-updated without follow-up reads carry thousands of uncleaned transaction slots. A full scan over such blocks must check undo per block to confirm cleanout state — multiplying undo demand far beyond the query's logical work. The 01555 trace naming cleanout is the signature: the query isn't reading hot history, it's paying cleanout tax on cold blocks.

The cure sits with the writers. Commit in sane batches (thousands of rows, not per-row and not once per 10M) so each commit's blocks get cleaned by subsequent reads promptly; follow massive loads with a DBMS_STATS gather or a light full scan that touches and cleans blocks while undo is fresh. Readers help by avoiding SELECT * full scans where indexed access suffices — fewer blocks touched, less cleanout owed.

Confirm before redesigning: the 01555 error trace (event 1555) distinguishes cleanout-driven failures from retention gaps, and AWR shows the writer's commit pattern. When cleanout dominates, writer hygiene plus a touch-pass fixes more than any retention raise — because the undo needed was never about query duration at all.

-- Writer commit sanity (commits/sec during load windows)
SELECT snap_id, value AS commits
FROM dba_hist_sysstat
WHERE stat_name = 'user commits'
ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;

-- Touch-pass after bulk loads (cleans blocks while undo is fresh)
-- (run post-load, off-peak)
-- SELECT /*+ FULL(o) */ COUNT(*) FROM shop.orders o;
-- EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP','ORDERS');

-- Readers: prefer indexed access over full scans where possible
EXPLAIN PLAN FOR SELECT * FROM shop.orders WHERE order_date > SYSDATE - 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Retention Math: Size Undo for Your Longest Query

Retention sizing is arithmetic, not folklore. From v$undostat: MAX(maxquerylen) is the longest query the system has seen (your requirement), and tuned_undoretention shows what space actually allowed. Set UNDO_RETENTION to maxquerylen plus 20% margin; then verify the tablespace holds retention-seconds × peak undo-block production (UNXPBLKRELCNT/UNXPBLKREUCNT trends per SECOND in the same view). If tuned retention sits below your setting, space binds — add datafiles until tuned meets the setting across a full workload cycle.

Watch the steal counters as your early warning: SSOLD (stolen expired blocks) climbing means retention promises are being broken for space; NOSPACEERRCOUNT nonzero means writers already failed under guarantee. Alert on both, plus maxquerylen crossing 80% of UNDO_RETENTION — the trend predicts the 01555 weeks before the marathon query meets the hot writer.

Recompute on cadence, not just after incidents. Workloads drift: new batch jobs raise undo production, new reports raise maxquerylen. A weekly v$undostat review (or an automated sizing report) keeps the garment fitted — retention sized once rots into the next 01555 within two quarters of steady workload drift.

-- Requirement: longest query seen (seconds)
SELECT MAX(maxquerylen) AS requirement_s,
  MAX(tuned_undoretention) AS tuned_s,
  SUM(ssold) AS stolen_blocks
FROM v$undostat;

-- Undo production rate per second (sizes the tablespace)
SELECT begin_time, undoblks AS undo_blocks,
  maxquerylen, tuned_undoretention
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;

-- Set 20% above the longest query, effective immediately
ALTER SYSTEM SET undo_retention = 26000 SCOPE = BOTH;

Prevention: Design Queries Shorter Than Retention

Infinite undo is not a design — queries must fit history, not the reverse. Break marathon rollups into incremental chunks (per-day partitions, committed progress, restartable from the last chunk) so no single query runs longer than retention. Prefer indexed range scans over full-table sweeps where the business question allows; push aggregation into materialized views refreshed incrementally. The 6-hour scan retired into 24 twenty-minute chunks is immune to 01555 by construction.

Isolate what remains long. Run unavoidable marathons on standby snapshots or flashback-guarded copies where writers can't churn the blocks being read — physical separation beats any retention number. Schedule them outside batch windows so even shared-primary runs face minimal churn, and fence batch writers from the scanned tables during the window.

Monitor the margin continuously: a sqlplus cron comparing v$undostat maxquerylen against UNDO_RETENTION pages when the gap narrows, long before any query dies. Retention pressure is a slow leak with a precise gauge — watch the gauge and the 3 AM pages stop. Put the margin graph on the same dashboard as batch runtimes so the team sees pressure and demand move together.

#!/bin/bash
# Page when longest queries approach retention (80% threshold)
set -euo pipefail
OUT=$(sqlplus -S 'mon/pass@//db-prod:1521/shop' << 'EOF'
SET HEADING OFF FEEDBACK OFF
SELECT MAX(maxquerylen) || ',' ||
  (SELECT TO_NUMBER(value) FROM v$parameter WHERE name='undo_retention')
FROM v$undostat;
EOF
)
MAXQ=$(echo "$OUT" | cut -d, -f1 | tr -d ' ')
RET=$(echo "$OUT" | cut -d, -f2 | tr -d ' ')
PCT=$(python3 -c "print(int(100*$MAXQ/$RET))")
[ "$PCT" -ge 80 ] && { echo "PAGE: retention margin ${PCT}% (maxq=$MAXQ ret=$RET)"; exit 1; }
echo "OK: retention margin ${PCT}%"