ORA-01555: Snapshot Too Old Error – Causes & Developer Fixes

The ORA-01555: Snapshot Too Old error is a common issue in Oracle databases, particularly during long-running queries or transactions. This error occurs when the database cannot maintain a consistent read view of data due to insufficient undo information. This developer’s guide provides a detailed, step-by-step approach to diagnosing and resolving ORA-01555 errors, with practical examples, real-world scenarios, pros and cons, and business use cases to help developers address this issue effectively.

---

What is the ORA-01555 Error?

The ORA-01555 error occurs when Oracle cannot provide a consistent read snapshot for a query or transaction because the required undo data has been overwritten. Oracle uses undo data to maintain read consistency, ensuring that queries see data as it existed at the start of the query or transaction. If the undo data is no longer available, Oracle throws the "Snapshot Too Old" error.

Common Causes of ORA-01555

1. Insufficient Undo Tablespace Size: The undo tablespace is too small to retain enough undo data for long-running queries or transactions.

2. Long-Running Queries: Queries that take a long time to execute may require undo data that has been overwritten.

3. Frequent Commits in Loops: Committing inside a loop during DML operations (e.g., UPDATE or DELETE) can prematurely release undo data.

4. High Transaction Volume: Heavy concurrent DML activity generates undo data rapidly, overwriting older undo segments.

5. Inadequate Undo Retention: The UNDO_RETENTION parameter is set too low, causing undo data to expire too quickly.

6. Delayed Block Cleanout: Uncommitted transactions or delayed block cleanouts can increase the demand for undo data.

---

Step-by-Step Guide to Diagnose and Resolve ORA-01555

Step 1: Identify the Problematic Query or Transaction

When ORA-01555 occurs, Oracle provides a generic error message like:

ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small

Examine the query or transaction that triggered the error. Look for:

• Long-running SELECT statements, especially those scanning large tables.

• DML operations (e.g., UPDATE, DELETE) with frequent commits in loops.

• PL/SQL blocks or batch jobs processing large datasets.

Example: A report query scanning a large table:

SELECT COUNT(*), department_id
FROM employee_history
WHERE hire_date < SYSDATE - 365
GROUP BY department_id;

If this query runs for hours and fails with ORA-01555, it’s likely due to insufficient undo data.

Step 2: Check Undo Tablespace Configuration

Verify the size and settings of the undo tablespace:

1. Check Undo Tablespace Size:

   SELECT tablespace_name, sum(bytes)/1024/1024 AS mb_size
   FROM dba_data_files
   WHERE tablespace_name = 'UNDO_TBS'
   GROUP BY tablespace_name;

2. Check Undo Retention:

   SELECT name, value
   FROM v$parameter
   WHERE name = 'undo_retention';

   Typical Output:

   NAME           | VALUE
   ---------------|-------
   undo_retention | 900

   The default undo_retention (in seconds) is often 900 (15 minutes), which may be too low for long-running queries.

3. Check Undo Usage:

   SELECT tablespace_name, status, sum(bytes)/1024/1024 AS mb_used
   FROM dba_undo_extents
   GROUP BY tablespace_name, status;

   This shows how much undo space is active, expired, or unexpired.

Step 3: Analyze the Query or Transaction

Determine if the query or transaction is unnecessarily long-running or inefficient:

• Check Execution Plan:

  EXPLAIN PLAN FOR
  SELECT COUNT(*), department_id
  FROM employee_history
  WHERE hire_date < SYSDATE - 365
  GROUP BY department_id;
  
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  Look for full table scans or missing indexes that prolong execution.

• Identify Frequent Commits: Check for PL/SQL code like:

  FOR rec IN (SELECT * FROM employee_history) LOOP
      UPDATE employee_history
      SET status = 'ARCHIVED'
      WHERE employee_id = rec.employee_id;
      COMMIT;
  END LOOP;

  Committing inside a loop releases undo data, increasing the risk of ORA-01555.

Step 4: Apply Fixes

Choose an appropriate solution based on the cause:

Solution 1: Increase Undo Tablespace Size

If the undo tablespace is too small, increase its size or enable autoextend.

Example:

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UNDO_TBS.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

Check New Size:

SELECT tablespace_name, sum(bytes)/1024/1024 AS mb_size
FROM dba_data_files
WHERE tablespace_name = 'UNDO_TBS'
GROUP BY tablespace_name;

Solution 2: Increase Undo Retention

Set a higher UNDO_RETENTION to retain undo data longer.

Example:

ALTER SYSTEM SET undo_retention = 3600; -- 1 hour

For Automatic Undo Management (AUM), Oracle may still overwrite unexpired undo if space is needed, so combine this with a larger tablespace.

Solution 3: Optimize Long-Running Queries

Rewrite or optimize queries to reduce execution time.

Example: Add an index to improve the report query:

CREATE INDEX idx_employee_history_hire_date ON employee_history(hire_date);

Re-run the query and check the execution plan to confirm improvement.

Solution 4: Avoid Commits in Loops

Move commits outside loops to preserve undo data.

Bad Practice:

FOR rec IN (SELECT * FROM employee_history) LOOP
    UPDATE employee_history
    SET status = 'ARCHIVED'
    WHERE employee_id = rec.employee_id;
    COMMIT;
END LOOP;

Fixed Code:

BEGIN
    UPDATE employee_history
    SET status = 'ARCHIVED';
    COMMIT;
END;
/

Solution 5: Use Guaranteed Undo Retention

Enable guaranteed undo retention to prevent Oracle from overwriting unexpired undo data.

Example:

ALTER TABLESPACE UNDO_TBS RETENTION GUARANTEE;

Check Setting:

SELECT tablespace_name, retention
FROM dba_tablespaces
WHERE tablespace_name = 'UNDO_TBS';

Note: This may cause the database to run out of undo space, so monitor usage carefully.

Solution 6: Break Up Large Transactions

Process large datasets in smaller chunks to reduce undo requirements.

Example:

DECLARE
    v_batch_size NUMBER := 1000;
    v_last_id NUMBER := 0;
BEGIN
    LOOP
        UPDATE employee_history
        SET status = 'ARCHIVED'
        WHERE employee_id > v_last_id
        AND employee_id <= v_last_id + v_batch_size
        AND ROWNUM <= v_batch_size;
        
        COMMIT;
        
        v_last_id := v_last_id + v_batch_size;
        EXIT WHEN SQL%ROWCOUNT = 0;
    END LOOP;
END;
/

Solution 7: Tune Automatic Undo Management

Ensure the undo tablespace is configured for Automatic Undo Management (AUM):

SELECT name, value
FROM v$parameter
WHERE name IN ('undo_management', 'undo_tablespace');

Expected Output:

NAME              | VALUE
------------------|---------
undo_management   | AUTO
undo_tablespace   | UNDO_TBS

If undo_management is MANUAL, switch to AUTO:

ALTER SYSTEM SET undo_management = 'AUTO' SCOPE = SPFILE;
-- Restart the database

Step 5: Test the Fix

Rerun the query or transaction to confirm the error is resolved. Monitor undo usage during execution:

SELECT tablespace_name, status, sum(bytes)/1024/1024 AS mb_used
FROM dba_undo_extents
GROUP BY tablespace_name, status;

Test with edge cases, such as high transaction loads or concurrent queries.

---

Real-Life Example: Financial Reporting System

Scenario

A financial reporting system runs a daily report querying a large transactions table. The query takes several hours and fails with ORA-01555.

Table Structure:

CREATE TABLE transactions (
    transaction_id NUMBER PRIMARY KEY,
    account_id NUMBER,
    amount NUMBER,
    transaction_date DATE
);

INSERT INTO transactions
SELECT level, mod(level, 100), level * 10, SYSDATE - level
FROM dual
CONNECT BY level <= 1000000;

Problematic Query:

SELECT account_id, SUM(amount)
FROM transactions
WHERE transaction_date < SYSDATE - 365
GROUP BY account_id;

Error: ORA-01555: snapshot too old

Solution

1. Check Undo Configuration:

   SELECT tablespace_name, sum(bytes)/1024/1024 AS mb_size
   FROM dba_data_files
   WHERE tablespace_name = 'UNDO_TBS';

   Output: 50 MB (too small).

2. Increase Undo Tablespace:

   ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UNDO_TBS.dbf' RESIZE 500M;

3. Set Undo Retention:

   ALTER SYSTEM SET undo_retention = 7200; -- 2 hours

4. Optimize Query: Create an index:

   CREATE INDEX idx_transactions_date ON transactions(transaction_date);

5. Break Up Query: Process data by date ranges:

   SELECT account_id, SUM(amount)
   FROM transactions
   WHERE transaction_date BETWEEN SYSDATE - 730 AND SYSDATE - 365
   GROUP BY account_id
   UNION ALL
   SELECT account_id, SUM(amount)
   FROM transactions
   WHERE transaction_date < SYSDATE - 730
   GROUP BY account_id;

6. Test and Monitor: Rerun the query and monitor undo usage to ensure stability.

---

Pros and Cons of Handling ORA-01555

Pros

• Improved Reliability: Increasing undo retention or tablespace size ensures consistent query execution.

• Optimized Performance: Query tuning reduces execution time, minimizing ORA-01555 risks.

• Scalability: Proper undo management supports high transaction volumes.

Cons

• Storage Overhead: Larger undo tablespaces require more disk space.

• Performance Trade-offs: Guaranteed retention may cause undo space exhaustion.

• Complexity: Tuning queries or breaking up transactions adds development effort.

---

Usage in Real Life and Business

Real-Life Usage

1. Batch Processing: ETL jobs or data archiving processes that handle large datasets often trigger ORA-01555.

2. Reporting Systems: Long-running analytical queries for financial or operational reports.

3. Data Warehousing: Queries scanning historical data in data warehouses.

4. Concurrent Transactions: High-volume systems with frequent DML operations.

Business Use Cases

1. Financial Systems: Monthly account reconciliation reports may fail if undo data is overwritten.

2. E-Commerce: Inventory audits or sales reports scanning large transaction tables.

3. Healthcare: Patient data analysis or billing reports requiring consistent historical data.

4. Telecommunications: Call detail record (CDR) analysis for usage patterns.

Best Practices for Businesses

• Monitor Undo Usage: Regularly check dba_undo_extents to detect space issues.

• Tune Queries: Use indexes and partitioning to optimize long-running queries.

• Avoid Frequent Commits: Structure DML operations to commit at the end of transactions.

• Set Realistic Undo Retention: Balance retention with available storage.

• Use Guaranteed Retention Sparingly: Reserve for critical queries to avoid space exhaustion.

• Automate Alerts: Configure alerts for undo tablespace usage thresholds.

PL/SQL Example for Error Handling:

BEGIN
    FOR rec IN (SELECT account_id, SUM(amount) AS total
                FROM transactions
                WHERE transaction_date < SYSDATE - 365
                GROUP BY account_id) LOOP
        DBMS_OUTPUT.PUT_LINE('Account: ' || rec.account_id || ', Total: ' || rec.total);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -1555 THEN
            DBMS_OUTPUT.PUT_LINE('ORA-01555: Snapshot too old. Increase undo retention or optimize query.');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        END IF;
END;
/

---

Conclusion

The ORA-01555: Snapshot Too Old error is a challenging but manageable issue in Oracle databases, often caused by insufficient undo tablespace, long-running queries, or frequentcommits. By increasing undo retention, optimizing queries, and restructuring transactions, developers can resolve this error effectively. In business contexts, addressing ORA-01555 ensures reliable reporting and data processing in financial, e-commerce, and healthcare systems. Adopting best practices like query tuning, undo monitoring, and robust error handling helps maintain database performance and prevents disruptions in critical operations.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone