ORA-01555 Snapshot Too Old: Reproduce It, Then Make It Impossible

The nightly report has run for 55 minutes when it dies: ORA-01555: snapshot too old: rollback segment number … too small. You didn’t change the query. The data is fine. Run it again at 2 a.m. and it works. So you shrug, add a retry, and move on — until it fails during the quarter-end close and someone asks why.

Here’s the part that flips the whole problem on its head: ORA-01555 is not your query being wrong. It’s your query being right. A long-running statement is supposed to return data as it looked when the query started, even while the rest of the world keeps changing it. When Oracle can no longer reconstruct that consistent picture, it refuses to hand you a wrong one — and that refusal is ORA-01555. This post is what’s actually happening, why it’s almost always an undo-configuration problem rather than a query problem, the fix that makes it impossible, and a lab that forces the error on demand and then cures it with the same query.

What snapshot too old actually is

Oracle gives every query read consistency: the result reflects a single point in time — the moment the query began (technically, its start SCN) — no matter how long it runs or who commits changes underneath it. To pull that off, when a query reads a block that has been modified since it started, Oracle rebuilds the older version of that block from undo (the before-images every change generates, kept in the undo tablespace).

ORA-01555 happens when the undo needed to rebuild that older block is no longer there. Undo isn’t kept forever; once it’s older than undo_retention it’s considered expired and its space can be reused by new transactions. If a long query is still running when the undo it depends on gets overwritten by a burst of DML, the read can’t be made consistent — and Oracle raises ORA-01555 instead of returning data from two different points in time. The error names a “rollback segment too small,” but the real message is: the undo I needed to show you a consistent picture was recycled out from under me.

That reframing matters because it tells you where to look. The query isn’t broken. The undo configuration couldn’t cover a query that long against that much change.

Why it happens

Three things line up to produce a snapshot-too-old:

• A long-running query — a report, an export, an ETL scan, a cursor fetched slowly in a loop. The longer it runs, the further back the undo it may need reaches.
• Heavy concurrent DML generating lots of undo — every update and delete writes before-images, and a busy system churns through undo fast.
• Undo that can’t cover the gap — an undersized undo tablespace, an undo_retention shorter than the query, and — the subtle one — no guarantee that unexpired undo is actually kept.

That last point is the trap. undo_retention is a target, not a promise. By default Oracle treats it as best-effort: under space pressure it will happily overwrite undo that is younger than undo_retention rather than fail a DML statement. So you can set undo_retention to an hour, watch a burst of updates fill the undo tablespace, and have your 40-minute report die anyway — because the database chose to protect the writers, not your reader. The fix is to make that a real guarantee, which we’ll get to.

flowchart TD
Q["Long query starts — pins snapshot at SCN 100"] --> R["reads early blocks as of SCN 100"]
R --> C["Meanwhile: heavy DML rewrites the rows<br/>and commits, churning undo"]
C --> W{"Undo tablespace fills.<br/>What gives?"}
W -->|"small + NOGUARANTEE"| O["overwrite unexpired undo<br/>→ the SCN-100 undo is gone"]
O --> F["next fetch needs SCN-100 undo<br/>→ ORA-01555"]
W -->|"sized + RETENTION GUARANTEE"| K["keep unexpired undo<br/>(fail a DML with ORA-30036 first)"]
K --> D["query reads every row,<br/>consistent as of SCN 100"]

Reproduce it

You don’t have to wait for it in production. Build a 20,000-row table on a deliberately weak undo config — 25 MB, no autoextend, RETENTION NOGUARANTEE, undo_retention = 1 — then run one query that spans a burst of change:

1. Open a full-scan cursor and fetch one row. That pins the read-consistent snapshot at the current SCN.
2. Now rewrite every row twelve times, committing each pass — churning far more undo than the 25 MB tablespace can hold, so it wraps and overwrites the undo the cursor’s snapshot depends on.
3. Keep fetching. The very next block the cursor reads was changed after its snapshot, its rebuild-undo is gone, and the fetch dies:

ORA-01555 raised? YES   (rows fetched before it died: 1, sqlcode -1555)

The query didn’t do anything wrong — it asked for a consistent read and the undo to provide it had been recycled. That’s snapshot-too-old, reproduced deterministically.

The fix that makes it impossible

Now change nothing about the query — only the undo:

• Size the undo tablespace for the workload so it can hold the undo generated during your longest query plus the churn around it. (300 MB in the lab; measure yours — see the FAQ.)
• Set undo_retention to at least the duration of your longest-running query — if your worst report runs 90 minutes, undo_retention needs to cover 90 minutes.
• Turn undo_retention into a promise with RETENTION GUARANTEE on the undo tablespace. Now Oracle will never overwrite unexpired undo — it protects the reader.

Re-run the exact same query and the identical churn, and it reads all 20,000 rows with no error:

ORA-01555 raised? NO   (rows fetched: 20000)

Don’t take my word for it — run it. The snapshot-too-old lab builds the table on Oracle Database Free, forces ORA-01555 on the 25 MB NOGUARANTEE undo tablespace, then swaps in a 300 MB tablespace with RETENTION GUARANTEE and undo_retention = 1200 and runs the same query to completion. It asserts the failure reproduces and the fix cures it. If the error doesn’t happen — or survives the fix — the run fails. Proven on every CI push.

There is one trade-off to know, and it’s the point of the feature: with RETENTION GUARANTEE, when the undo tablespace genuinely runs out of room, Oracle fails the DML with ORA-30036: unable to extend segment … in undo tablespace rather than sacrifice a reader. That is the correct choice for reporting and ETL windows — you would rather a write wait or fail than have a long read return inconsistent data — but it means you must size the undo tablespace for the write workload too, or your DML starves. Guarantee protects reads; sizing protects writes; you need both.

What teams get wrong

• Treating it as a query bug. ORA-01555 is almost never fixed by rewriting the SQL. It’s an undo-coverage problem: the query outran the undo kept for it. Look at undo sizing and retention first.
• Trusting undo_retention without RETENTION GUARANTEE. Retention is best-effort by default; under pressure Oracle overwrites unexpired undo to keep writers moving. If a long read must stay consistent, guarantee it — and size for the consequence.
• The fetch-across-commit anti-pattern. The classic self-inflicted 1555: OPEN a cursor, then loop fetching and COMMIT-ing inside the loop (updating the very rows you’re reading). Each commit lets that undo expire and be reused, so the same session’s cursor destroys the undo it still needs. Don’t commit inside a cursor loop over the same data; do set-based DML, or fetch into a collection first.
• Autoextend off with nobody watching. A fixed-size undo tablespace is fine — until the workload grows and it can no longer cover the longest query. Either autoextend it or monitor headroom; don’t let it silently become too small.
• Guessing the retention instead of measuring it. V$UNDOSTAT records the longest query and the undo generation rate in ten-minute buckets, and TUNED_UNDORETENTION shows what Oracle actually enforced. Size from those, not from a hunch.
• Confusing undo with redo. Undo (before-images, for read consistency and rollback) is not redo (the change log, for recovery). ORA-01555 is always an undo problem; a full or slow redo/archive area is a different failure. Fix the right one.

Snapshot-too-old sits on the same foundation as the rest of Oracle’s read-consistency and recovery story: undo is what lets Flashback Query rewind a table to an earlier SCN, the same before-images that protect a long report protect a bad UPDATE; when recovery is the problem instead, that’s RMAN; and lock contention that stalls the writers is its own deadlock story. ORA-01555 is the reader’s side of the same mechanism — treat it as an undo-coverage problem, size undo and undo_retention for your longest query, make it a promise with RETENTION GUARANTEE, and then prove it the way that ends the argument: with the snapshot-too-old lab, where the same query dies on a starved undo tablespace and finishes on a guaranteed one.