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.

Same long query, two undo configs. It pins its snapshot at the SCN it started (100 here) and rebuilds any block changed since then from undo. On a small, NOGUARANTEE undo tablespace, a burst of DML overwrites that undo to make room — so the next fetch has nothing to rebuild from and dies with ORA-01555. On a sized tablespace with RETENTION GUARANTEE, unexpired undo is kept (Oracle fails a DML with ORA-30036 before overwriting it), and the query finishes 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.

Frequently asked questions

What causes ORA-01555 snapshot too old?

ORA-01555 occurs when a long-running query needs to reconstruct an older, read-consistent version of a block from undo, but that undo has already been overwritten. Every query returns data as of the moment it started, and when it reads a block that was changed after it started, Oracle rebuilds the earlier version using the undo (before-images) generated by those changes. If heavy concurrent DML has churned through the undo tablespace and reused the space holding the undo the query still needs — typically because the undo tablespace is undersized, undo_retention is shorter than the query, or unexpired undo is not guaranteed — the read can no longer be made consistent and Oracle raises ORA-01555 rather than return data from two different points in time. It is fundamentally an undo-configuration and workload problem, not a defect in the query.

Is ORA-01555 a bug in my query?

No. ORA-01555 is read consistency working as designed: the query is correctly refusing to return a result stitched together from different points in time. The query being long is what exposes the problem, but the cause is that the undo needed to keep it consistent was recycled before it finished. That said, one query pattern actively causes it — the fetch-across-commit anti-pattern, where a cursor is fetched in a loop that also commits changes to the same rows, letting the session overwrite the undo its own cursor depends on. Outside that pattern, the fix is almost always in undo configuration (sizing, undo_retention, and RETENTION GUARANTEE), not in rewriting the SQL.

How do I fix ORA-01555?

Fix it in the undo configuration, in three parts. First, set undo_retention to at least the duration of your longest-running query, so undo is meant to be kept long enough. Second, add RETENTION GUARANTEE to the undo tablespace so that undo_retention becomes a hard promise and Oracle will never overwrite unexpired undo. Third, size the undo tablespace large enough (or let it autoextend) to hold the undo generated during that longest query plus the concurrent DML churn, because with RETENTION GUARANTEE a too-small tablespace makes writes fail with ORA-30036 instead. Measure the numbers from V$UNDOSTAT rather than guessing. Rewriting the query, adding retries, or restarting the report are workarounds, not fixes.

What is undo_retention and is it a guarantee?

undo_retention is a parameter, in seconds, that tells Oracle how long you would like committed undo to be kept before its space can be reused. By default it is only a target: under space pressure Oracle will overwrite undo that is younger than undo_retention rather than fail a DML, so a long-running query can still hit ORA-01555 even with a generous undo_retention. It becomes a real floor only when you set RETENTION GUARANTEE on the undo tablespace. Note also that with an autoextending undo tablespace Oracle automatically tunes retention upward based on the longest recent query (visible as TUNED_UNDORETENTION in V$UNDOSTAT), but tuning is still best-effort without the guarantee.

What is RETENTION GUARANTEE and what is the downside?

RETENTION GUARANTEE is a setting on the undo tablespace (ALTER TABLESPACE undo_ts RETENTION GUARANTEE) that instructs Oracle never to overwrite unexpired undo — undo younger than undo_retention — even if that means it cannot find space for a new transaction. It is what turns undo_retention from a target into a promise, and it is the reliable cure for ORA-01555 on long reads. The downside is the flip side of the promise: when the undo tablespace genuinely fills, a DML statement fails with ORA-30036 (unable to extend segment in the undo tablespace) instead of being allowed to overwrite protected undo. That is usually the right trade for reporting and ETL windows — a write waiting or failing is better than a read returning inconsistent data — but it means you must size the undo tablespace for the write workload as well, or DML will starve.

What is the fetch-across-commit anti-pattern?

Fetch-across-commit is a coding pattern that causes ORA-01555 from within a single session: you OPEN a cursor over a set of rows, then loop fetching from it while also COMMITting updates to those same rows inside the loop. Each commit lets the undo for those changes expire and become reusable, and as the loop continues the session overwrites the very undo its own still-open cursor needs to stay read-consistent — so a later fetch fails with snapshot too old. The fix is to not commit inside a cursor loop that reads the data being changed: use a single set-based UPDATE or DELETE instead, or fetch the driving keys into a collection first and then process them, or commit far less frequently. It is one of the few cases where ORA-01555 really is caused by the code rather than the undo configuration.

How do I size the undo tablespace and undo_retention correctly?

Measure, using V$UNDOSTAT, which records ten-minute buckets of undo activity. MAXQUERYLEN shows the longest query duration observed, which is the minimum your undo_retention should cover; UNDOBLKS with the block size gives the undo generation rate, which tells you how much undo accumulates over that retention window; and TUNED_UNDORETENTION shows the retention Oracle actually enforced. Size undo_retention to at least MAXQUERYLEN (with headroom), and size the undo tablespace to hold the undo generated over that period plus concurrent DML — the DBA_HIST_UNDOSTAT view (with Diagnostics Pack) extends this history across AWR. Then set RETENTION GUARANTEE so the retention is actually honored, and monitor for ORA-30036 as the signal that the tablespace, not the retention, needs to grow.

Does ORA-01555 mean I am losing data?

No. ORA-01555 is a protection, not corruption: Oracle raises it precisely to avoid returning inconsistent data, so the query stops rather than producing a wrong result. No committed data is lost, nothing is rolled back incorrectly, and the database remains fully consistent — only the individual long-running query fails and can be rerun. It is a signal that your undo configuration cannot cover queries of that length against that much change, and the correct response is to fix undo sizing, undo_retention, and RETENTION GUARANTEE so the query can complete, not to worry about data integrity.

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.

Originally published at uptimearchitect.com.