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: SYSDBA or SELECT_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:

Plaintext

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:

Plaintext

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:

1. UNDO retention expired: The time elapsed exceeded UNDO_RETENTION, and Oracle reused those undo blocks for newer transactions.

2. 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.

3. Fetch-Across-Commit: A PL/SQL cursor loops over a dataset, executes UPDATE, and calls COMMIT inside 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.

SQL

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:

SQL

SHOW PARAMETER undo;

Example Output:

Plaintext

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:

SQL

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:

SQL

-- 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:

SQL

-- 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:

SQL

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Warning: If UNDO space runs out with RETENTION GUARANTEE enabled, new DML transactions (INSERT/UPDATE/DELETE) will fail with ORA-30036 instead of queries failing with ORA-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:

SQL

-- 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):

SQL

-- 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:

SQL

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:

$$\text{Undo Size}=\text{Undo Retention (sec)}\times \text{Undo Blocks per Second}\times \text{Block Size}$$

Run this query during peak load hours to calculate recommended sizing:

SQL

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 COMMIT inside a row-by-row cursor loop.

• Monitor long-running queries via V$SESSION_LONGOPS.