ORA-01555 Snapshot Too Old — Real Fix for Oracle

ORA-01555 snapshot too old happens when undo data needed for a consistent read gets overwritten. Bump undo retention, stop bad long-running queries, and fix cursor loops that fetch across commits.

Quick answer

ORA-01555 fires when Oracle needs an older version of a block to keep a query consistent, but the undo holding that version is already gone. Increase UNDO_RETENTION, make sure the undo tablespace has room to grow, and kill the long-running query that's dragging its feet.

What's actually happening

Oracle gives every query a read-consistent view of the data as of the moment the query started. If you kick off a report at 09:00 that scans 40 million rows, Oracle has to reconstruct that 09:00 picture of every block it touches — even if rows changed at 09:15. That reconstruction comes from undo. If undo gets overwritten (because it got old, or because the tablespace filled up and Oracle had to recycle), you get ORA-01555 and the whole query dies.

People see "snapshot too old" and think it's a disk space problem. It's not, usually. It's a retention problem, or a poorly written query that holds a cursor open way longer than it should. I've seen this bite teams on a 2TB undo tablespace because the app was doing fetch-across-commit in a loop for six hours.

Fix it — numbered steps

Find the query that blew up. Check the alert log and the trace file Oracle dumped. The SQL is right there.
```
grep -A50 "ORA-01555" $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log
```
You'll see the SQL text and the undo segment it needed.
Check current undo retention. If it's the default 900 seconds and your query runs for 2 hours, that's your problem.
```
SHOW PARAMETER undo_retention;
SELECT begin_time, end_time, tuned_undoretention
  FROM v$undostat
 ORDER BY begin_time DESC
 FETCH FIRST 10 ROWS ONLY;
```
tuned_undoretention is what Oracle is actually honoring — look at the max value. If it's far below how long your worst query runs, bump it.
Raise undo_retention. Set it to cover your longest legitimate query, plus a safety margin. For a 2-hour ETL report, set 10800 (3 hours).
```
ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH SID='*';
```
Don't set it to 30 days and forget it — that just delays the inevitable and wastes undo space.

Make sure the undo tablespace can actually hold that much. Retention is a wish until the tablespace has the room. Check usage:

SELECT tablespace_name, status, SUM(bytes)/1024/1024 mb
  FROM dba_undo_extents
 GROUP BY tablespace_name, status;

SELECT name, value/1024/1024 mb
  FROM v$parameter
 WHERE name = 'undo_tablespace';

If UNEXPIRED is tiny and EXPIRED is huge, you don't have enough space for the retention you asked for. Add a datafile:

ALTER TABLESPACE UNDOTBS1
  ADD DATAFILE '/u01/oradata/ORCL/undotbs02.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

Kill the long-running query if it's still hanging around. Sometimes it's a runaway query nobody's watching. Find it:

SELECT s.sid, s.serial#, s.username, s.sql_id,
       ROUND(s.last_call_et/60,1) mins_running
  FROM v$session s
 WHERE s.status = 'ACTIVE'
   AND s.last_call_et > 3600
 ORDER BY s.last_call_et DESC;

Then ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;.

If that doesn't fix it

Bumping retention is the fix about 70% of the time. The other 30% is a code problem, and no amount of undo will save you.

The fetch-across-commit pattern

This is the classic. The app opens a cursor, fetches 100 rows, commits, keeps fetching. Every commit lets Oracle advance the SCN and recycle undo. Eventually the cursor tries to read a block whose undo is gone. The fix isn't settings — it's the loop. Fetch everything you need into a collection first, then commit. Or bump the cursor to SELECT ... FOR UPDATE if the business logic truly requires it (but talk to the app team first, that has its own costs).

Delayed block cleanout

Massive UPDATE or DELETE operations leave cleanout work owed on the blocks. When a long-running query stumbles across those blocks later, it has to do the cleanout itself — which requires undo that may already be gone. Running DBMS_STATS.GATHER_TABLE_STATS after big DML helps force that work to happen at a sane time. It's not a guarantee, but it's cheap.

Undo tablespace too small

If you've inherited an undo tablespace that's 1GB and the DB does 50GB/hour of change, you're not going to fix this with parameters. Size the undo based on peak DML plus your retention window. A rule of thumb: undo size ≈ (undo generation rate per second × retention seconds). Measure it with v$undostat before you guess.

Stopping it from coming back

Most ORA-01555 incidents I've dealt with were documented as "known app behavior" for months before someone actually fixed the fetch-across-commit loop. Don't be that team.

Set retention based on your longest acceptable query, alert on tuned_undoretention dropping below that, and put code review eyes on any cursor that commits mid-fetch. That's it. No magic. The error is Oracle telling you it can't keep a promise it made to your query — respect the promise by giving it the undo to keep.