Explaining the ORA-1555 Error: Causes, Solutions, and Prevention

If you operate an Oracle database, you may have experienced the headache of the “ORA-1555: snapshot too old” error. This error occurs when a long-running query becomes unable to reference “past data.”

Why does this error occur? How can it be fixed?

By reading this article, you can grasp the full picture of the ORA-1555 error and gain practical solutions.

1.What is Read Consistency?

An Oracle database is designed so that even if multiple transactions are running concurrently, each query can consistently reference the data state as of its start time.

Terminology

UNDO Segment An area that records the state of data before it was changed. → It reproduces past data “like a time machine.”
Snapshot The data state at the time the query execution began.

Figure 1: How Read Consistency Works

+------------------------+   SELECT statement   +------------------------+
| Data Block (Latest)    | -----------------> | Data at Query Start    |
|                        |                      | (Restored from UNDO Segment)|
+------------------------+                      +------------------------+

2.Causes of the ORA-1555 Error

The ORA-1555 error occurs in the following situations.

1. Insufficient UNDO Data

If the UNDO tablespace is small, the past data required by long-running queries will be overwritten.

Figure 2: Example of UNDO Data Shortage

+---------+------------------+------------------+
| Time    | UNDO Data        | Query Status     |
+---------+------------------+------------------+
| 0 min   | A, B, C          | Query Start      |
| 10 min  | D, E, F (A is lost)| Needs A          |
+---------+------------------+------------------+

2. Long-running Queries

Queries with long execution times consume a large amount of UNDO data, causing the error.

3. Poor Tablespace Design

This occurs when the UNDO tablespace is not set to auto-extend or its size is too small.

4. Excessive Transactions

The error occurs when numerous transactions run concurrently, putting pressure on (exhausting) the UNDO data.

3.Detailed Cause Diagnosis Procedures

Execute the following to identify the cause of the error.

1. Check the Alert Log

tail -f $ORACLE_BASE/diag/rdbms/<SID>/trace/alert_<SID>.log

2. Analyze UNDO Usage

SELECT BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT, MAXQUERYLEN
FROM V$UNDOSTAT
WHERE BEGIN_TIME >= SYSDATE - 1;

3. Identify Long-running Queries

SELECT SID, SERIAL#, SQL_ID, TIME_REMAINING
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING > 0;

4.Effective Solutions and SQL Examples

1. Appropriately Extend the UNDO Tablespace

Secure the necessary UNDO data by increasing the UNDO tablespace size.

ALTER DATABASE DATAFILE '/path_to_undo/undotbs01.dbf' RESIZE 2G;
ALTER DATABASE DATAFILE '/path_to_undo/undotbs01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Adjust UNDO_RETENTION

Prevent the error by extending the UNDO data retention period.

ALTER SYSTEM SET UNDO_RETENTION = 1800; -- 30 minutes

3. Optimize Long-running Queries

Reduce UNDO consumption by breaking up long-running queries.

BEGIN
   FOR i IN 1..10 LOOP
      EXECUTE IMMEDIATE 'DELETE FROM large_table WHERE ROWNUM <= 1000';
      -- Note: In a real scenario, you'd likely add a COMMIT here,
      -- but be aware this breaks transaction atomicity.
   END LOOP;
END;

4. Improve Application Design

Reduce contention by distributing transactions and implementing proper index design.

5.Operational Best Practices for Prevention

Monitor the UNDO Tablespace

Periodically check UNDO usage.

SELECT TABLESPACE_NAME, BYTES/1024/1024 AS MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'UNDOTBS1';

Refactor Queries

Break up long-running queries into multiple smaller transactions.

Proper Index Design

Add indexes to frequently accessed data.

Configure UNDO Tablespace Auto-extend

Enable auto-extend to ensure the tablespace does not hit its space limit.

6.Case Study: Real-world Problems and Their Solutions

Case 1: ORA-1555 due to a Long-running Query

Problem: Occurred during nightly batch processing. Query execution time exceeded 1 hour, leading to insufficient UNDO data.
Solution:
- Extended the UNDO tablespace to 4GB.
- Broke the query into 10,000-row chunks (with commits).
- Adjusted UNDO_RETENTION to 1800 seconds.

Case 2: Transaction Contention

Problem: Excessive transactions during peak daytime hours caused frequent ORA-1555 errors.
Solution:
- Rebuilt indexes to reduce contention.
- Batched update transactions to distribute the load.

Summary

The “ORA-1555: snapshot too old” error is caused by issues with UNDO data or query design. You can minimize the occurrence of this error by taking the following measures:

Optimizing UNDO tablespace size
Adjusting UNDO_RETENTION
Designing long-running queries to be broken up
Monitoring and proactive management

Please incorporate these points into your operations to improve database stability!

[reference]
Managing Undo – Database Administrator’s Guide