Fix ORA-01555 snapshot too old: real causes and cures

Quick answer

For the impatient: the immediate fix is to increase undo retention and make sure your undo tablespace can actually hold that much data — but if your query is running for hours, you're treating the symptom, not the disease. Tune the query or use flashback query.

What's actually happening here

Oracle gives every query a read-consistent snapshot of the data as of the moment the query started. To build that snapshot, it reads the current blocks and then rolls them back using undo (rollback) segments. If the undo needed for a block has been overwritten by newer transactions, you get ORA-01555.

The classic scenario: a long-running SELECT joins a huge table, and somewhere in the middle of the scan, another session updates a block that hasn't been read yet. Oracle tries to reconstruct the pre-update image, but the undo for that old version is gone. Boom.

Notice that this isn't always about the undo tablespace being too small. Sometimes it's a query that's just too slow, or a commit storm that chews through undo faster than you'd expect. The error can also pop up in PL/SQL loops where you commit inside a cursor — a nasty one, because you're destroying your own read consistency.

How to fix it: numbered steps

1. Find the actual undo retention that's in effect. Run this and check the tuned_undoretention value:

   SELECT retention, tuned_undoretention, begin_time
   FROM v$undostat
   ORDER BY begin_time DESC FETCH FIRST 1 ROW ONLY;

   If the tuned value is way lower than your expected query duration, that's your problem.
2. Set a sane undo_retention. Don't just guess. Look at your longest-running query (from v$long_ops or DBA_HIST_SQLSTAT). Set undo_retention to at least that duration plus a safety margin. Example:

   ALTER SYSTEM SET undo_retention=1800 SCOPE=BOTH;

   That's 30 minutes, not a magic number. Adjust to your workload.
3. Make sure the undo tablespace can actually hold that retention. If it's fixed size and fills up, Oracle will reuse space even if it's younger than the retention. Check current size and grow it:

   ALTER TABLESPACE undo ADD DATAFILE '/u01/oracle/undo02.dbf' SIZE 10G AUTOEXTEND ON;

   Or if you're on 12c+, enable autoextend on the existing datafiles. If your undo is already autoextending, but the disk is full, that's your bottleneck.
4. If you're on 12c or later, consider the automatic tuning. Oracle automatically tunes undo retention based on query length. But it's capped by the undo tablespace size. If you want to force a minimum, set undo_retention explicitly — that overrides the automatic tuning only partially. Actually, the automatic tuning will still raise it above your setting, but it won't go below it. So setting a floor is fine.
5. For the specific case of PL/SQL commits inside loops: Stop doing that. If you must commit periodically, use a savepoint and rollback to savepoint to manage locks without destroying your snapshot. But better: restructure the code to avoid commits inside the cursor loop. The reason step 3 works is that each commit releases undo, so a long-running cursor that commits every 100 rows will age out its own undo super fast.

If the main fix doesn't work: alternative approaches

Sometimes you can't just throw more undo at the problem. Here's what else actually works.

Tune the query

A query that runs in 5 minutes instead of 2 hours is far less likely to hit this error. Look at the execution plan. Often it's a full table scan that could be a hash join, or a missing index causing nested loops with millions of iterations. Use SQL Tuning Advisor or just eyeball the plan. The point is: a faster query needs less undo retention.

Use flashback query

If you absolutely must read a consistent view of data from a specific time in the past, use AS OF TIMESTAMP instead of relying on the default read consistency. That way Oracle uses undo differently — but careful: this still needs enough undo to cover the time gap. It's not a miracle worker.

Partition the table

If you're scanning a huge table and only need a subset, use partition pruning to reduce the number of blocks scanned. Fewer blocks touched = less chance of hitting a recycled undo.

Increase the UNDO tablespace size aggressively

You might think you've sized it fine, but the error keeps coming back. Check v$undostat for the maxquerylen — that's the longest query duration. If it's close to your retention, you're on the edge. Add 50% more space. Disk is cheap.

Prevention: don't let it happen again

Here's the thing — ORA-01555 is a symptom, not a disease. The real prevention is to understand your workload's undo consumption and query durations.

• Monitor undo usage weekly. Query v$undostat and look at trends. If tuned_undoretention is below your target, you need more space.
• Set alert thresholds. Use OEM or a simple script to alert when undo exceeds 80% of the tablespace.
• Review long-running queries regularly. Any query that runs longer than the undo retention is a candidate for tuning or breaking up.
• Be careful with batch jobs. If you run nightly batches that update millions of rows, they'll flood the undo. Schedule them when other activity is low, and consider committing in batches — but as noted, not inside a cursor that's being used for the query itself.

One more thing: if you're on Oracle 11g or earlier, the automatic undo tuning is less aggressive. You might need to manually set undo_retention higher than the default 900 seconds. On 12c+ it's smarter, but it still needs enough space to do its thing.

The real fix is a combination: right-sized undo, reasonable retention, and queries that don't run forever. Get those three in line, and ORA-01555 becomes a rarity instead of a nightly annoyance.