How to Fix ORA-01555: Snapshot Too Old in Oracle (Diagnostic & Prevention Guide)
Quick Summary: ORA-01555 occurs when a long-running query requires consistent read data from undo segments, but that undo data has already been overwritten. This guide covers how to identify the failing query, trace undo tablespace pressure, and resolve the issue through UNDO tuning and query optimization.
1. Environment & Prerequisites
Database Versions: Oracle 11g, 12c, 18c, 19c, 23ai
OS Platform: Linux / Unix / Windows
Privileges Required:
SYSDBAorSELECT_CATALOG_ROLE
2. Problem Description & Error Stack
During long batch jobs, night reporting runs, or data export tasks (expdp), the executing session fails and writes an entry to the alert log:
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_123456789$" too small
ORA-02063: preceding line from REMOTE_DB
In the application log or SQL*Plus session, you will see:
SQL> SELECT * FROM large_orders_mv;
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8_342129038$" too small
3. Root Cause Analysis
Oracle uses Read Consistency (Multi-Version Concurrency Control / MVCC). When Query A starts at 10:00 AM, Oracle guarantees that Query A will only see data as it existed at 10:00 AM.
If Query B updates and commits rows at 10:05 AM, the old image of those rows moves to the UNDO tablespace. If Query A reaches those updated blocks at 10:30 AM, it must go to UNDO to reconstruct the 10:00 AM version.
ORA-01555 happens when:
UNDO retention expired: The time elapsed exceeded
UNDO_RETENTION, and Oracle reused those undo blocks for newer transactions.Delayed Block Cleanout: A massive uncommitted transaction modified blocks, and a subsequent SELECT query tried to clean them out long after the undo logs disappeared.
Fetch-Across-Commit: A PL/SQL cursor loops over a dataset, executes
UPDATE, and callsCOMMITinside the loop.
4. Step-by-Step Solution & Diagnostics
Step 1: Identify the Failing Query & Duration
First, check V$UNDOSTAT to see when the undo tablespace experienced high usage or retention steals around the time of the error.
SELECT
TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS start_time,
TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI') AS end_time,
ssolderrcnt AS snapshot_too_old_errors,
nospaceerrcnt AS no_space_errors,
maxquerylen AS max_query_seconds,
tuned_undoretention AS auto_tuned_retention
FROM v$undostat
WHERE ssolderrcnt > 0
ORDER BY begin_time DESC;
If maxquerylen is larger than tuned_undoretention, your query simply takes longer to run than the historical undo data is retained.
Step 2: Check Your UNDO Tablespace Configuration
Check the current size, auto-extension settings, and parameter values:
SHOW PARAMETER undo;
Example Output:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Next, verify if your UNDO tablespace is configured with fixed retention guarantees:
SELECT tablespace_name, retention FROM dba_tablespaces WHERE contents = 'UNDO';
Step 3: Apply the Fix
Depending on the underlying cause identified in your environment, apply one of the three solutions below.
Option A: Increase UNDO Retention and Space (Most Common)
If your batch query runs for 2 hours (7,200 seconds), set UNDO_RETENTION higher than the longest query duration:
-- Increase retention target to 4 hours (14,400 seconds)
ALTER SYSTEM SET undo_retention = 14400 SCOPE=BOTH;
Ensure the UNDO datafile has enough space to hold 4 hours of transaction history:
-- Allow UNDO tablespace to grow automatically if disk space permits
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 30G;
Option B: Enforce Retention Guarantee
By default, Oracle can overwrite unexpired UNDO blocks if the UNDO tablespace runs out of space. Force Oracle to protect undo data for the full duration of UNDO_RETENTION:
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
Warning: If UNDO space runs out with
RETENTION GUARANTEEenabled, newDMLtransactions (INSERT/UPDATE/DELETE) will fail withORA-30036instead of queries failing withORA-01555. Use this only when you have sufficient disk space.
Option C: Fix PL/SQL "Fetch-Across-Commit" Anti-Pattern
If the error occurs inside a PL/SQL procedure, inspect the code for commits inside a cursor loop:
Incorrect Code Pattern:
-- DO NOT DO THIS
FOR r IN (SELECT id, status FROM orders WHERE status = 'PENDING') LOOP
UPDATE orders SET status = 'PROCESSED' WHERE id = r.id;
COMMIT; -- <--- This breaks the cursor's read consistency over time!
END LOOP;
Correct Code Pattern (Bulk Processing):
-- Recommended Approach: Use Bulk Collect & FORALL
DECLARE
TYPE t_order_ids IS TABLE OF orders.id%TYPE;
v_ids t_order_ids;
BEGIN
SELECT id BULK COLLECT INTO v_ids
FROM orders
WHERE status = 'PENDING';
FORALL i IN 1..v_ids.COUNT
UPDATE orders SET status = 'PROCESSED' WHERE id = v_ids(i);
COMMIT; -- Commit ONCE after the batch is processed
END;
/
5. Verification
After increasing UNDO_RETENTION or refactoring the PL/SQL code, re-execute the target process and monitor V$UNDOSTATin real time:
SELECT
TO_CHAR(begin_time, 'HH24:MI:SS') AS sample_time,
maxquerylen,
tuned_undoretention,
ssolderrcnt
FROM v$undostat
WHERE begin_time > SYSDATE - (1/24);
Expected Result: ssolderrcnt remains 0, and tuned_undoretention stays higher than maxquerylen.
6. Sizing Formula & Prevention
To calculate the exact UNDO size required for your workload, use this formula:
Run this query during peak load hours to calculate recommended sizing:
SELECT
((UR * UPS * 8192) / (1024 * 1024)) AS recommended_undo_mb
FROM
(SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT MAX(undoblks/600) AS UPS FROM v$undostat);
Best Practices:
Keep statistics updated (
DBMS_STATS.GATHER_TABLE_STATS) on large tables to prevent long-running queries caused by bad execution plans (FTS instead of Index Scans).Never
COMMITinside a row-by-row cursor loop.Monitor long-running queries via
V$SESSION_LONGOPS.
Labels: 19c, ORA-, snapshot too old
