SOLVED

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

Asked by OracleDba••29 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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.
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
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.
OracleDba•

Post Your Solution