Fix ORA-01555 Snapshot Too Old Error

You're running a long batch report at 2 AM—maybe a year-end rollup or a data warehouse refresh—and it dies with ORA-01555: snapshot too old. The query ran fine last week. Nothing changed in the code. But tonight, it's dead. This error is infuriating because it's not a syntax problem; your SQL is perfect. The issue is that Oracle's undo data—the stuff it uses to give you a consistent read of the database as it was when your query started—was overwritten before your query finished.

Here's the simple explanation: when your query starts, Oracle takes a mental snapshot of the data at that moment. As other transactions modify rows, Oracle stores the old versions in the undo tablespace. If your query runs long enough, and the undo tablespace is too small or the retention period is too short, those old versions get recycled. When Oracle tries to read a block that's been overwritten, it throws ORA-01555.

The root cause is almost always one of three things: an undersized undo tablespace, a too-short UNDO_RETENTION setting, or—and this is the sneaky one—your query is hitting a huge number of blocks that are being modified concurrently. Let's fix it.

Step 1: Check Your Current Undo Settings

First, log into SQL*Plus and run this:

SHOW PARAMETER undo_management;
SHOW PARAMETER undo_retention;
SELECT tablespace_name, retention FROM dba_tablespaces WHERE tablespace_name = 'UNDOTBS1';

If undo_management is MANUAL, stop right here—you're running in rollback segment mode, which is ancient. Switch to automatic undo management (AUM) if you can, but that's a bigger change. For this article, I'll assume you're on AUM with undo_retention set to something like 900 seconds (15 minutes).

Your undo tablespace name might be different. Check with:

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';

Step 2: Increase Undo Retention

The quickest fix is to extend the retention period. This tells Oracle to keep undo data around longer before overwriting it. You can do this on the fly:

ALTER SYSTEM SET undo_retention = 3600; -- 1 hour, adjust as needed

But here's the catch: retention is only a guarantee if your undo tablespace is big enough. If the tablespace fills up, Oracle will overwrite even within the retention period. So step 3 is just as important.

Step 3: Size Up the Undo Tablespace

Check how full your undo tablespace is:

SELECT tablespace_name, bytes/1024/1024 AS size_mb,
       (SELECT SUM(bytes)/1024/1024 FROM dba_undo_extents WHERE tablespace_name = 'UNDOTBS1') AS used_mb
FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1';

If used space is over 80% of total, you need to add more space. You can add a datafile:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/oradata/undotbs02.dbf' SIZE 2G;

Or resize existing datafiles. I prefer adding a new one—it's less risky than resizing and can be dropped later if needed. Also, make sure you have the RETENTION GUARANTEE set on the tablespace. This forces Oracle to keep undo even if it means transactions fail with space errors, which is better than your query failing with ORA-01555. Here's how:

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

I've seen this single setting save many a late-night batch job. Without it, Oracle will steal undo space from your long-running query the moment a short update needs it.

Step 4: Tune the Problem Query

Sometimes the undo retention is fine, but your query is too slow. If it's taking hours, no retention setting will save you. Look at the execution plan:

EXPLAIN PLAN FOR SELECT ...;

Look for full table scans on huge tables. If you're scanning a 10 GB table and there's a lot of concurrent DML on that table, you're asking for trouble. Consider adding indexes to reduce the number of blocks you read, or partition the table so your query only touches relevant partitions.

Another trick: break the query into smaller chunks using a cursor loop or by adding a WHERE ROWNUM condition. This reduces the time your snapshot is active.

Still Failing? Here's What to Check

If you've increased retention, added space, and still see ORA-01555, look at the alert log for messages about undo segment extension. You might have a corrupted undo segment—though rare, it happens. Run:

SELECT * FROM v$undostat ORDER BY begin_time DESC;

This shows undo statistics for the last 24 hours. Check the maxquerylen column—if your query runs longer than the maximum query length that undo stats show, you'll never catch up. You need to either speed up the query or increase retention further.

Also, check if you're hitting the “delayed block cleanup” issue. This happens when a transaction commits but leaves some blocks in a “dirty” state, and a later query has to roll back those changes to get a consistent read. It's a known Oracle quirk. If you see lots of “cleanout” waits in v$session_wait, consider running EXEC DBMS_STATS.GATHER_TABLE_STATS on the affected tables to refresh the block cleanliness.

One more thing: if you're using Oracle RAC, make sure your undo tablespace is shared properly across instances. Sometimes one instance has a smaller undo tablespace, and the error only appears when your query lands on that instance.

The full fix is often a combination of these steps. Start with RETENTION GUARANTEE and a bigger undo tablespace—that solves 80% of cases. If it doesn't, dig into the query itself.

I've been there, staring at an ORA-01555 at 3 AM, and it's never fun. But now you know exactly what to check. Good luck.