ORA-01555 Snapshot Too Old - Complete Resolution Guide

ORA-01555: Snapshot Too Old

Error Overview

Error Text: ORA-01555: snapshot too old: rollback segment number string with name "string" too small

The ORA-01555 error occurs when Oracle cannot reconstruct a read-consistent image of data because the required undo information has been overwritten. This is one of the most common errors in Oracle databases, particularly in systems with long-running queries or reports.

Common Causes

1. Insufficient Undo Retention

• Undo retention period shorter than longest query runtime
• Automatic undo management not properly configured
• Manual undo segments too small

2. Long-Running Queries

• Reports running for hours
• Complex analytical queries
• Queries against tables with high DML activity

3. Delayed Block Cleanout

• Blocks not cleaned after large transactions
• Full table scans triggering cleanout
• Insufficient ITL slots

4. Fetch Across Commits

• Cursor loops with commits inside
• Application design issues
• Improper transaction boundaries

Diagnostic Queries

Check Current Undo Configuration

-- Undo tablespace information
SELECT tablespace_name, status, retention
FROM dba_tablespaces
WHERE contents = 'UNDO';

-- Undo parameters
SHOW PARAMETER undo;

-- Current undo usage
SELECT
  TO_CHAR(begin_time, 'DD-MON-YY HH24:MI:SS') as begin_time,
  TO_CHAR(end_time, 'DD-MON-YY HH24:MI:SS') as end_time,
  undoblks,
  maxquerylen,
  maxquerysqlid,
  unxpstealcnt,
  ssolderrcnt
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time DESC;

Identify Problem Queries

-- Find long-running queries
SELECT
  s.sid, s.serial#, s.username, s.sql_id,
  s.last_call_et/60 as minutes_running,
  q.sql_text
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.last_call_et > 3600  -- Running > 1 hour
ORDER BY s.last_call_et DESC;

-- Historical ORA-01555 errors
SELECT
  sample_time,
  session_id,
  sql_id,
  event,
  program
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 7
  AND (event LIKE '%ORA-01555%'
       OR session_state = 'ON CPU' AND sql_opname = 'SELECT')
ORDER BY sample_time DESC;

Analyze Undo Requirements

-- Calculate required undo retention
SELECT
  MAX(maxquerylen) as max_query_seconds,
  ROUND(MAX(maxquerylen)/60, 2) as max_query_minutes,
  MAX(maxquerysqlid) as longest_query_sqlid
FROM v$undostat
WHERE begin_time > SYSDATE - 7;

-- Undo space usage patterns
SELECT
  TO_CHAR(begin_time, 'HH24') as hour,
  ROUND(AVG(undoblks * 8192 / 1024 / 1024), 2) as avg_undo_mb,
  MAX(undoblks * 8192 / 1024 / 1024) as max_undo_mb,
  AVG(unxpstealcnt) as avg_unexpired_stolen,
  SUM(ssolderrcnt) as snapshot_errors
FROM v$undostat
WHERE begin_time > SYSDATE - 1
GROUP BY TO_CHAR(begin_time, 'HH24')
ORDER BY hour;

Resolution Steps

1. Immediate Solutions

Increase Undo Retention

-- Set undo retention (in seconds)
ALTER SYSTEM SET undo_retention = 7200 SCOPE=BOTH;  -- 2 hours

-- Enable retention guarantee
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Add Undo Space

-- Check current undo tablespace size
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb,
  ROUND(SUM(maxbytes)/1024/1024/1024, 2) as max_size_gb
FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
GROUP BY tablespace_name;

-- Add datafile to undo tablespace
ALTER TABLESPACE undotbs1
ADD DATAFILE '/u01/oradata/undo02.dbf'
SIZE 10G AUTOEXTEND ON MAXSIZE 32G;

2. Long-Term Solutions

Optimize Undo Management

-- Enable automatic undo management
ALTER SYSTEM SET undo_management = AUTO SCOPE=SPFILE;

-- Set appropriate undo tablespace
ALTER SYSTEM SET undo_tablespace = UNDOTBS1 SCOPE=BOTH;

-- Enable automatic tuning
ALTER SYSTEM SET "_undo_autotune" = TRUE SCOPE=BOTH;

Application Changes

-- Example: Fix fetch across commit
-- BAD: Commits inside cursor loop
DECLARE
  CURSOR c1 IS SELECT * FROM large_table;
BEGIN
  FOR rec IN c1 LOOP
    process_record(rec);
    COMMIT;  -- This causes ORA-01555
  END LOOP;
END;

-- GOOD: Bulk collect with periodic commits
DECLARE
  TYPE t_data IS TABLE OF large_table%ROWTYPE;
  l_data t_data;
  CURSOR c1 IS SELECT * FROM large_table;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO l_data LIMIT 1000;
    EXIT WHEN l_data.COUNT = 0;

    FOR i IN 1..l_data.COUNT LOOP
      process_record(l_data(i));
    END LOOP;

    COMMIT;  -- Commit after bulk processing
  END LOOP;
  CLOSE c1;
END;

3. Performance Tuning

Optimize Long-Running Queries

-- Create indexes for problematic queries
CREATE INDEX idx_problem_columns
ON problem_table(col1, col2)
TABLESPACE indexes;

-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');

-- Use parallel query for large scans
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER TABLE large_table PARALLEL 8;

Implement Query Result Caching

-- Enable result cache
ALTER SYSTEM SET result_cache_mode = MANUAL SCOPE=BOTH;
ALTER SYSTEM SET result_cache_max_size = 100M SCOPE=BOTH;

-- Use result cache hint
SELECT /*+ RESULT_CACHE */
  column1, column2
FROM large_table
WHERE conditions;

Prevention Strategies

1. Monitoring Setup

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_undo_usage AS
  v_retention NUMBER;
  v_guarantee VARCHAR2(10);
  v_steal_count NUMBER;
BEGIN
  -- Check retention settings
  SELECT retention INTO v_retention
  FROM dba_tablespaces
  WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

  -- Check unexpired steal count
  SELECT SUM(unxpstealcnt) INTO v_steal_count
  FROM v$undostat
  WHERE begin_time > SYSDATE - 1/24;  -- Last hour

  IF v_steal_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: Undo segments being stolen! Count: ' || v_steal_count);
    -- Send alert
  END IF;
END;
/

-- Schedule monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_UNDO_JOB',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_undo_usage',
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

2. Best Practices

Undo Sizing Formula

-- Calculate required undo size
-- UNDO_SIZE = UNDO_RETENTION * UPS * OVERHEAD
-- Where:
-- UNDO_RETENTION = desired retention in seconds
-- UPS = Undo blocks per second
-- OVERHEAD = 1.1 (10% safety margin)

WITH undo_calc AS (
  SELECT
    7200 as target_retention,  -- 2 hours
    AVG(undoblks/((end_time - begin_time) * 86400)) as ups
  FROM v$undostat
  WHERE begin_time > SYSDATE - 7
)
SELECT
  target_retention,
  ROUND(ups, 2) as undo_blocks_per_sec,
  ROUND(target_retention * ups * 8192 / 1024 / 1024 / 1024 * 1.1, 2) as required_undo_gb
FROM undo_calc;

Configuration Recommendations

• Set undo_retention to at least 20% more than longest query
• Use RETENTION GUARANTEE for critical systems
• Monitor undo usage during peak hours
• Size undo tablespace for peak + 50% overhead

Related Errors

• ORA-30036 - Unable to extend undo segment
• ORA-01650 - Unable to extend rollback segment
• ORA-01628 - Max # extents reached for rollback segment

Emergency Response

Quick Fixes

1. Kill problematic session

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. Temporarily increase undo

   ALTER SYSTEM SET undo_retention = 10800 SCOPE=MEMORY;

3. Add emergency undo space

   ALTER TABLESPACE undotbs1 ADD DATAFILE '/tmp/undo_temp.dbf' SIZE 5G;

Post-Resolution Cleanup

-- Reset temporary changes
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

-- Remove temporary datafile
ALTER TABLESPACE undotbs1
DROP DATAFILE '/tmp/undo_temp.dbf';

-- Analyze root cause
@?/rdbms/admin/awrrpt.sql