Oracle databases are powerful, but when something goes wrong during long-running queries, few errors are as confusing and frustrating as:
ORA-01555: snapshot too old
This error usually appears when you run long SELECT statements, reports, batch jobs, or queries across large tables. It often shows up only in production environments and not in dev or testing — and that’s exactly why many DBAs find it tricky to diagnose.
But here’s the good news: ORA-01555 is completely fixable once you understand what Oracle is telling you.
What ORA-01555 Really Means
Oracle’s official message says:
ORA-01555: snapshot too old: rollback segment number with name “” too small
What does that mean in real life?
Here’s the simple version:
Your query needs old data (a “snapshot”) to maintain read consistency,
but Oracle already cleaned that old data out of UNDO before your query finished.
Oracle uses UNDO (previously rollback segments) to keep old versions of blocks so that long queries can see a consistent state of data — even while other users are updating the same rows.
If the UNDO gets overwritten before your query reads it → Oracle can’t reconstruct the old snapshot → ORA-01555.
Think of it like watching security camera footage while the system overwrites the earlier part of the video. When you rewind to find the recording — it’s gone.
Why ORA-01555 Happens — Real Production Causes
Here are the MOST common reasons this happens.
1. UNDO retention too low
UNDO_RETENTION defines how long Oracle tries to keep old undo data.
If it’s too small, queries lose their snapshots quickly.
2. Long-running SELECT queries
A report or batch query that runs for 30 minutes or 2 hours may be trying to read data that has already been overwritten.
3. Heavy DML activity happening at the same time
Large updates, deletes, or batch loads can overwrite UNDO faster than normal.
Common in:
- ETL pipelines
- ERP systems
- Banking workloads
- Month-end batch jobs
4. UNDO tablespace too small
Even if UNDO_RETENTION is set high, Oracle cannot honor it if the tablespace has no space to store the data.
UNDO_RETENTION is “best effort,” not guaranteed unless UNDO tablespace is big enough.
5. Poorly designed queries
Queries that repeatedly read the same blocks may experience “delayed block cleanout,” leading to ORA-01555.
6. Frequent commits inside loops (DEV mistake)
This is extremely common in PL/SQL or application code:
FOR r IN (SELECT ... ) LOOP
UPDATE table SET ...;
COMMIT;
END LOOP;
This pattern guarantees ORA-01555 by constantly overwriting undo needed by the same loop.
NEVER commit inside loops.
Period.
How to Diagnose ORA-01555 (Step-By-Step)
✔ 1. Check the current UNDO retention:
SHOW PARAMETER undo_retention;
✔ 2. Check size of undo tablespace:
SELECT tablespace_name, SUM(bytes)/1024/1024 AS MB
FROM dba_data_files
WHERE tablespace_name='UNDOTBS1'
GROUP BY tablespace_name;
✔ 3. Check how much undo is being consumed:
SELECT status, SUM(bytes)/1024/1024 MB
FROM dba_undo_extents
GROUP BY status;
✔ 4. Check which query caused the issue (if logged):
SELECT sql_text
FROM v$sql
WHERE sql_id='&sqlid';
How to Fix ORA-01555 — Proven Solutions
Below are the real solutions DBAs use in production, not theoretical ones.
FIX 1: Increase UNDO_RETENTION
If UNDO expires too quickly, extend it.
Recommended baseline:
ALTER SYSTEM SET undo_retention = 900; -- 15 minutes
For heavy systems:
ALTER SYSTEM SET undo_retention = 3600; -- 1 hour
ALTER SYSTEM SET undo_retention = 7200; -- 2 hours
FIX 2: Increase the size of UNDO tablespace
UNDO retention is never guaranteed unless the tablespace is big enough.
Add a datafile:
ALTER TABLESPACE undotbs1
ADD DATAFILE '/u01/app/oracle/oradata/undo02.dbf' SIZE 1G;
Or resize:
ALTER DATABASE DATAFILE 'undo01.dbf' RESIZE 2G;
FIX 3: Stop committing inside loops
This is one of the MAIN causes.
Wrong:
FOR rec IN (...) LOOP
UPDATE table ...
COMMIT;
END LOOP;
Correct:
FOR rec IN (...) LOOP
UPDATE table ...
END LOOP;
COMMIT;
FIX 4: Rewrite or tune long-running queries
Issues:
- Full table scans
- Bad join conditions
- Missing indexes
- Functions on indexed columns
Example tuning:
- Add missing index
- Rewrite joins
- Limit returned rows
- Use partition pruning
- Avoid correlated subqueries
FIX 5: Reduce DML workload during reporting windows
Schedule:
- Reports and analytics during low DML times
- Batch updates outside business hours
FIX 6: Switch UNDO tablespace (advanced)
Sometimes UNDO gets fragmented.
Switch temporarily:
CREATE UNDO TABLESPACE undotbs2 DATAFILE 'undo2.dbf' SIZE 2G;
ALTER SYSTEM SET undo_tablespace = undotbs2;
Then drop the old one later.
Advanced Fix: Guarantee UNDO Retention
If your environment requires exact UNDO availability:
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
⚠ Warning:
Oracle will NOT overwrite undo, even if the database runs out of space — can cause DML failures.
Use only when necessary.
Real-World Example
A financial system ran an end-of-day reporting job that took 45 minutes. During the day, the database had very high update activity.
UNDO retention was 600 seconds (10 minutes).
UNDO tablespace was too small (8 GB).
Solution:
- Increased UNDO retention to 3600 seconds
- Added 10 GB to UNDO tablespace
After these changes, the report ran without errors.
Best Practices to Prevent ORA-01555
- Size UNDO based on workload, not guesswork
- Avoid commits inside loops
- Tune long-running queries
- Monitor UNDO usage daily
- Increase retention during reporting loads
- Separate OLTP and reporting workloads if possible
Final Thoughts
The ORA-01555: Snapshot Too Old error is one of the most misunderstood Oracle errors, but it’s actually very logical once you understand how Oracle handles UNDO and read consistency.
In summary:
- Your query needed old data
- UNDO overwrote it before your query finished
- Increasing UNDO size + retention, tuning queries, and stopping commit abuse prevents the problem
With the solutions above, you can eliminate ORA-01555 from your production systems and ensure smooth long-running queries — even during peak activity.
