Pradip
26 Aug, 2025
0 Comments
6 Mins Read

ORA-01555: Snapshot Too Old – Causes, Prevention & Fixes

Introduction

If you’ve been working as an Oracle DBA for some time, I can almost guarantee you’ve faced the infamous ORA-01555: Snapshot Too Old error at least once. Personally, I still remember the first time I got this error, it was on a production database during month-end reporting, and the developers were chasing me asking, “Why did my query suddenly fail?”

At that time, I had no clue what undo tablespaces were doing behind the scenes. Later, I realized this error is not just “common,” it’s actually one of the most evergreen errors in Oracle databases. Even today, whether you’re on 11g, 12c, 19c, or the latest 23c, ORA-01555 continues to haunt DBAs.

In this article, I’ll take you step by step through what this error means, why it happens, real-world cases where you’ll see it, and most importantly, how you can fix and prevent it like a pro.

So, let’s break it down together.

What is ORA-01555: Snapshot Too Old?

The error usually looks like this:

ORA-01555: Snapshot Too Old: rollback segment number X with name "..." too small

In simple words:

Oracle needs undo data to reconstruct a consistent read for your query.
If that undo data is overwritten (because the undo tablespace is too small or reused), Oracle can’t fetch the old snapshot.
As a result, your query fails with ORA-01555.

Think of undo as a time machine. If the time machine runs out of “fuel,” you can’t go back in time to reconstruct the old data. That’s what “snapshot too old” means.

Why Does ORA-01555 Happen? (The Causes)

Let’s break down the most common causes you’ll see in real life.

1. Insufficient Undo Tablespace

The undo tablespace is too small to handle the workload.
Long-running queries need consistent undo for old blocks. If undo runs out, Oracle overwrites older undo.

👉 Example: You have a query scanning millions of rows while other transactions are updating the same table. Undo for the old rows gets overwritten, and boom, ORA-01555.

2. Bad Query Design (Fetching in Loops)

When developers fetch rows one-by-one in PL/SQL loops with a cursor, Oracle may reuse the same undo multiple times.
This is one of the most common mistakes in reporting jobs or ETL scripts.

👉 Example: A PL/SQL procedure with FETCH … INTO … inside a LOOP instead of bulk collect.

3. Long-Running Queries with Concurrent DML

You’re running a heavy SELECT query. Meanwhile, DML (INSERT/UPDATE/DELETE) operations are modifying the same data.
Undo is consumed by these DMLs, and the SELECT can’t reconstruct older versions.

👉 Example: Running month-end reports while batch jobs are updating sales tables.

4. Poor Undo Management (Autoextend OFF)

Undo tablespace not configured with AUTOEXTEND ON.
When it runs out of space, Oracle has no choice but to reuse undo segments prematurely.

5. Undo Tablespace Fragmentation

Even if undo is “large enough,” fragmentation inside undo can cause failures.
Oracle cannot allocate the required contiguous space for undo blocks.

6. LOBs and Undo

Large Objects (CLOB, BLOB) consume significant undo during updates.
If undo is not managed with SecureFiles or NOCACHE settings, snapshot errors are very common.

Real-World Scenarios Where You’ll See ORA-01555

Let me share some practical DBA war stories:

Case 1: A reporting query fetching 10M+ rows was failing every night with ORA-01555. The issue? Developers were using FETCH NEXT in loops. Solution: rewrote to use bulk collect and increased undo size.
Case 2: Undo tablespace was just 500MB on a production DB running high-volume transactions. During month-end, the system was throwing ORA-01555 like crazy. Fix: resized undo to 4GB with autoextend.
Case 3: A CLOB column was being updated in a batch process. Undo tablespace kept getting filled. Solution: used SecureFiles LOBs with DISABLE STORAGE IN ROW and NOCACHE.

These are the kinds of problems you will face as a DBA in the real world.

How to Troubleshoot ORA-01555

When you get ORA-01555, don’t just resize undo blindly. Follow a systematic troubleshooting approach:

Step 1: Check Undo Tablespace Usage

SELECT tablespace_name, file_name, autoextensible, bytes/1024/1024 AS MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';

👉 Check if undo is big enough and if AUTOEXTEND is enabled.

Step 2: Look at Current Undo Usage

SELECT a.tablespace_name, a.file_id, a.bytes/1024/1024 AS size_mb, 
       (a.bytes - b.bytes_used)/1024/1024 AS free_mb
FROM dba_data_files a, v$temp_space_header b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name LIKE 'UNDO%';

👉 Helps you see whether undo is actually running out.

Step 3: Analyze SQL Causing the Error

Look for long-running queries in AWR or V$SQL.
If PL/SQL code is fetching row-by-row, that’s a red flag.

Step 4: Check for LOBs

SELECT table_name, column_name, segment_name
FROM dba_lobs
WHERE table_name = '<TABLE_NAME>';

👉 If LOBs are involved, enable SecureFiles.

Step 5: Monitor Undo Parameters

UNDO_MANAGEMENT should be set to AUTO.
Check UNDO_TABLESPACE and ensure you are using the correct one.
Look at UNDO_RETENTION parameter (it defines how long undo is retained for consistent read).

How to Fix ORA-01555 (The Solutions)

Here are the practical fixes I recommend:

1. Resize Undo Tablespace

ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' RESIZE 2G;
ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf' AUTOEXTEND ON;

2. Create a Bigger Undo Tablespace and Switch

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/oradata/undotbs02.dbf' SIZE 2G AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

3. Rewrite SQL/PLSQL

Use bulk collect instead of row-by-row fetching.
Avoid unnecessary full table scans.
Break queries into smaller chunks.

4. Tune Undo Retention

ALTER SYSTEM SET UNDO_RETENTION=900;

(But make sure undo size is large enough to support it.)

5. Optimize LOB Storage

Use SecureFiles instead of BasicFiles.
Use NOCACHE option for LOBs that don’t need undo.

6. Reduce Long Transactions

Commit in batches instead of one huge transaction.
For example, update 1M rows in chunks of 50K.

✅ This is the step-by-step technical solution set for ORA-01555.

Prevention Tips (Pro DBA Practices)

Here are some tips I personally follow to prevent ORA-01555 before it happens:

Always configure autoextend undo tablespace in production.
Monitor undo usage regularly using OEM or custom scripts.
Educate developers about bulk processing instead of loops.
Tune undo retention properly (not too low, not too high).
For ETL/reporting jobs, try to run them in read-only or reporting DB to reduce contention.
Keep a proactive alert, set up monitoring for undo tablespace >80% usage.

Final Thoughts

As an Oracle DBA, errors like ORA-01555 are part of the job. You and I will both agree that sometimes it feels frustrating because it always seems to appear in the worst possible moments, during month-end, quarter-end, or when a critical report is running.

But the good thing is, once you understand the root cause, undo management, this error becomes much easier to handle. In my own experience, 90% of the time the fix is either increasing undo size or fixing bad SQL code.

So, next time you see ORA-01555: Snapshot Too Old, don’t panic. Check undo usage, analyze the SQL, and apply the right fix. With the right approach, you can not only solve it but also prevent it from reappearing.

At Learnomate Technologies, we don’t just explain concepts – we make sure you understand them in the most practical way. That’s why we’re known for providing some of the best training in the industry for technologies like this.

👉 For quick insights and easy learning, hop onto our YouTube channel: www.youtube.com/@learnomate

👉 To explore our courses and services, check out our website: www.learnomate.org

👉 If you want to stay connected and keep learning together, follow me here on LinkedIn: Ankush Thavali

👉 And of course, if you want to read more about different technologies, you can always dive into our blog section here: https://learnomate.org/blogs/

Keep learning, keep growing, and remember – every expert was once a beginner who didn’t give up.