ORA-01555: snapshot too old in Oracle Database is a very common and critical performance issue faced by DBAs in production.

🔴 What ORA-01555 Means

👉 Oracle cannot reconstruct the older version of data needed by a query.

In simple terms:

Your query is running too long, and the required undo data has already been overwritten.

📌 Why This Happens

Oracle uses UNDO tablespace to maintain read consistency.

When a long-running query needs old data:

Oracle reads from UNDO
If UNDO is overwritten → ❌ ORA-01555

🔎 Common Causes

1️⃣ Long Running Queries

Query runs for a long time
Needs older data snapshot

2️⃣ Small UNDO Tablespace

Not enough space to retain old data

3️⃣ High DML Activity

Frequent INSERT / UPDATE / DELETE
Overwrites undo quickly

4️⃣ Commit Inside Loop (Very Common)

Bad code example:


FOR i IN 1..10000 LOOP
   UPDATE emp SET sal = sal + 100 WHERE empno = i;
   COMMIT;
END LOOP;

👉 This causes undo to be overwritten quickly.

5️⃣ Undo Retention Too Low

Check:


SHOW PARAMETER undo_retention;

🛠 Solutions

✅ 1️⃣ Increase UNDO Tablespace


ALTER DATABASE DATAFILE '/u01/undo01.dbf'
RESIZE 5G;

OR add new file:


ALTER TABLESPACE undotbs1
ADD DATAFILE '/u01/undo02.dbf' SIZE 2G;

✅ 2️⃣ Increase UNDO_RETENTION


ALTER SYSTEM SET undo_retention = 1800;

👉 (Value in seconds = 30 minutes)

✅ 3️⃣ Avoid Frequent Commits

❌ Wrong:


COMMIT inside loop

✅ Correct:


COMMIT after batch processing

✅ 4️⃣ Optimize Long Queries

Add indexes
Avoid full table scans
Break large queries into smaller chunks

✅ 5️⃣ Use RETENTION GUARANTEE (Advanced)


ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

⚠️ Use carefully → may block DML if space runs out.

🔎 Useful Monitoring Queries

Check undo usage


SELECT * FROM v$undostat;

Check undo tablespace size


SELECT tablespace_name, sum(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

Check active long queries


SELECT sid, serial#, sql_id, elapsed_time
FROM v$session_longops
WHERE sofar != totalwork;

📊 Real Production Scenario

A report query runs for 1 hour
Meanwhile heavy transactions run
Undo gets overwritten
Query fails with:


ORA-01555: snapshot too old

✅ Real Fix Implemented

1️⃣ Increased UNDO tablespace
2️⃣ Increased undo_retention
3️⃣ Optimized report query
4️⃣ Scheduled job during low load

📘 Key DBA Learning

👉 ORA-01555 is usually a combination of:

Long query +
Insufficient undo +
High DML activity

⭐ Best Practices

✔ Size UNDO properly
✔ Avoid commit inside loops
✔ Monitor long queries
✔ Tune SQL
✔ Schedule heavy jobs off-peak

Reference - ChatGpt

shashi

Tuesday, March 17, 2026

ORA-01555: snapshot too old