ORA-01555 Snapshot Too Old is one of the most frustrating Oracle errors DBAs encounter, especially during long-running reports or heavy batch operations. It happens when Oracle can no longer reconstruct the older version of queried data due to insufficient undo retention or aggressive undo reuse.
This error is directly linked to undo space management — specifically, Oracle’s ability to maintain read consistency for queries running over time.
How to tune undo tablespace and retention settings,
And practical ways to prevent it in real production systems.
What Is ORA-01555?
You might see this error while running a query:
ORA-01555: Snapshot too old: rollback segment number with name "" too small
Or in newer versions:
ORA-01555: snapshot too old: rollback segment too small
ORA-30036: unable to extend segment by N in undo tablespace UNDOTBS1
Essentially, Oracle is telling you:
“The undo information I needed to reconstruct your data is gone or overwritten.”
Why It Happens – The Real Cause
Oracle ensures read consistency by using undo records. When a query starts, Oracle reads data as of that point in time — even if other sessions modify it later. To maintain this view, Oracle stores the before image of changed data in the undo tablespace.
However, when:
Undo space is small,
Undo retention is too short, or
The undo data is reused for new transactions,
Oracle can no longer reconstruct that “old snapshot,” resulting in ORA-01555.
Common Scenarios Triggering ORA-01555
Scenario
Description
Long-running query
The query runs longer than undo retention, and old undo data gets overwritten.
Small undo tablespace
Undo data can’t be retained long enough to satisfy read consistency.
Frequent commits inside loops
Each commit invalidates previous undo, causing undo reuse.
High DML activity
Heavy updates and inserts overwrite existing undo faster.
Undo retention misconfigured
Undo_retention too low for the workload duration.
Example: The Problem in Action
Imagine a reporting query scanning 10 million rows. At the same time, another session is updating rows in the same table. If the query takes 30 minutes, but undo retention is set to 900 seconds (15 minutes), Oracle will reuse those undo blocks halfway through — leading to:
ORA-01555: snapshot too old
Step-by-Step Solution Guide
Step 1: Check Undo Tablespace Usage
Run:
SELECT tablespace_name, status, COUNT(*)
FROM dba_undo_extents
GROUP BY tablespace_name, status;
Status meanings:
ACTIVE: In use by ongoing transactions.
UNEXPIRED: Committed but still retained for read consistency.
EXPIRED: Available for reuse.
If most extents are EXPIRED, Oracle is reusing undo too quickly — increase undo space.
Step 2: Check Undo Retention
SHOW PARAMETER undo_retention;
If it’s low (like 900 seconds), increase it:
ALTER SYSTEM SET undo_retention=3600;
💡 Tip: Match undo_retention to your longest query duration.
Step 3: Enable Autoextend on Undo Datafiles
Ensure undo can grow as needed:
ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
Check:
SELECT file_name, autoextensible FROM dba_data_files WHERE tablespace_name='UNDOTBS1';
Step 4: Increase Undo Tablespace Size
If autoextend isn’t an option, resize manually:
ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' RESIZE 10G;
Or add a new file:
ALTER DATABASE ADD DATAFILE '/u02/oradata/UNDOTBS02.dbf' SIZE 5G AUTOEXTEND ON;
Step 5: Avoid Commits Inside Loops
Bad practice:
FOR rec IN (SELECT * FROM transactions) LOOP
INSERT INTO log_table VALUES (rec.id);
COMMIT; -- ❌ Frequent commits cause undo reuse
END LOOP;
Best practice:
FOR rec IN (SELECT * FROM transactions) LOOP
INSERT INTO log_table VALUES (rec.id);
END LOOP;
COMMIT; -- ✅ Single commit after loop
Step 6: Use Real-Time Undo Monitoring
SELECT begin_time, end_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC;
If maxquerylen > undo_retention, increase undo retention or space accordingly.
Step 7: Recreate Undo Tablespace (If Corrupted)
In rare cases where undo segments are corrupted or fragmented:
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u02/oradata/UNDOTBS02.dbf' SIZE 5G AUTOEXTEND ON;
ALTER SYSTEM SET undo_tablespace=UNDOTBS2;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Step 8: Tune Undo for Long Queries and Reports
For analytic or reporting systems:
Set undo_retention between 3600–7200 seconds.
Use bigger undo tablespaces (≥ 10 GB).
Schedule long queries during low DML activity periods.
Use materialized views or result cache for stable results.
Real-World Example
A retail data warehouse frequently failed during month-end reports with:
ORA-01555: snapshot too old: rollback segment too small
The ORA-01555: Snapshot Too Old error is not a bug — it’s a symptom of insufficient undo planning. By properly sizing undo tablespace, tuning undo retention, and optimizing transaction behavior, you can eliminate this error entirely.
The best DBAs don’t just fix undo errors — they design systems that never experience them again.
Comments 2