DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01555 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01555: Snapshot Too Old — What It Means and How to Fix It

ORA-01555 occurs when Oracle cannot reconstruct a consistent read of data because the undo information required for a query's start SCN (System Change Number) has already been overwritten. Essentially, your query started reading data at a specific point in time, but before it finished, Oracle recycled the undo blocks it needed. This error is most common in environments with heavy DML activity, undersized Undo Tablespaces, or long-running queries.

Top 3 Causes

1. Insufficient UNDO_RETENTION or Undo Tablespace Size

If UNDO_RETENTION is set too low, Oracle may recycle undo data before long queries finish reading. An undersized Undo Tablespace compounds this problem by forcing earlier recycling.

-- Check current Undo parameters
SHOW PARAMETER UNDO;

-- Increase UNDO_RETENTION to 3600 seconds (1 hour)
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE = BOTH;

-- Add a datafile to Undo Tablespace
ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/u01/oradata/orcl/undotbs02.dbf'
SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

-- Enable RETENTION GUARANTEE to prevent premature overwrite
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

-- Verify the setting
SELECT tablespace_name, retention
FROM dba_tablespaces
WHERE tablespace_name = 'UNDOTBS1';
Enter fullscreen mode Exit fullscreen mode

2. Long-Running Queries

Queries that run for extended periods — such as overnight batch jobs or large reports — are highly susceptible to ORA-01555. The longer a query runs, the higher the chance that its required undo data gets overwritten by concurrent DML transactions.

-- Identify long-running queries currently active
SELECT sql_id,
       elapsed_time / 1000000 AS elapsed_sec,
       sql_text
FROM v$sql
WHERE elapsed_time / 1000000 > 1800  -- queries running > 30 minutes
ORDER BY elapsed_time DESC;

-- Check undo statistics to assess risk
SELECT begin_time,
       end_time,
       undoblks,
       maxquerylen,
       ssolderrcnt  -- count of ORA-01555 occurrences
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 10 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

3. Fetch Across Commit (Bad Application Pattern)

Issuing a COMMIT inside a cursor loop — known as "fetch across commit" — is a common application-level cause of ORA-01555. After a commit, undo data for previously fetched rows may be released, breaking read consistency for the still-open cursor.

-- PROBLEMATIC pattern — avoid this
DECLARE
    CURSOR c1 IS SELECT order_id FROM orders WHERE status = 'PENDING';
BEGIN
    FOR rec IN c1 LOOP
        UPDATE orders SET status = 'PROCESSED'
        WHERE order_id = rec.order_id;
        COMMIT; -- Dangerous: commits inside open cursor
    END LOOP;
END;
/

-- RECOMMENDED pattern — use BULK COLLECT + FORALL
DECLARE
    TYPE t_ids IS TABLE OF orders.order_id%TYPE;
    l_ids t_ids;
BEGIN
    SELECT order_id
    BULK COLLECT INTO l_ids
    FROM orders WHERE status = 'PENDING';

    FORALL i IN 1..l_ids.COUNT
        UPDATE orders SET status = 'PROCESSED'
        WHERE order_id = l_ids(i);

    COMMIT; -- Single commit after all work is done
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- 1. Increase UNDO_RETENTION immediately
ALTER SYSTEM SET UNDO_RETENTION = 7200 SCOPE = BOTH;

-- 2. Check and expand Undo Tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name;

-- 3. Monitor how often ORA-01555 is happening
SELECT ssolderrcnt AS ora01555_count,
       nospaceerrcnt AS no_space_count,
       maxquerylen AS max_query_seconds
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 5 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Right-size your Undo Tablespace proactively.
Use v$undostat to calculate the recommended Undo size based on actual workload, and always enable AUTOEXTEND with a reasonable MAXSIZE. Set up monitoring alerts when usage exceeds 80%.

2. Redesign batch jobs to avoid long single transactions.
Break large data processing tasks into smaller chunks using BULK COLLECT with a LIMIT clause. Avoid cursor-level commits and keep transactions as short as possible. Schedule heavy batch workloads during off-peak hours to reduce Undo contention with OLTP transactions.

Related Errors

  • ORA-30036: Unable to extend undo segment — indicates Undo Tablespace is full, often a precursor to ORA-01555.
  • ORA-01562: Failed to extend rollback segment — related to Undo space exhaustion.
  • ORA-01628: Maximum extents reached for a rollback segment — signals poor Undo Tablespace management.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)