Understanding ORA-01555: Causes and Effective Fixes for Oracle Snapshots

What Is ORA-01555?

The ORA-01555 "snapshot too old" error is a common issue for Oracle DBAs and developers when a query cannot retrieve a consistent view of data because the required UNDO information has been overwritten.

Where to Find the Error Records

The error can appear in two main log files:

Alert log ("Alert" or "告警日志文件").

Trace log generated at the moment of the error. By default it is not created, but you can enable it with the command:

alter system set events '1555 trace name errorstack level 3';

After enabling the event, the trace file will be written whenever ORA-01555 occurs.

Root Causes of ORA-01555

The error is caused by the UNDO block that holds the snapshot being overwritten. Typical reasons include:

Expired UNDO records : The time between the transaction commit and the query exceeds the UNDO_RETENTION setting, allowing Oracle to reuse the space.

UNEXPIRED UNDO overwritten : This happens when the tablespace lacks RETENTION GUARANTEE and becomes full, so even active UNDO can be overwritten.

LOB consistency issues : Depending on whether LOB data is stored in‑row or out‑of‑row, the snapshot may be lost when the PCTVERSION limit or the RETENTION value for the LOB segment is exceeded.

Diagnostic Steps and Solutions

1. Examine the Error Logs

Check the alert log or the generated trace file for messages such as:

ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

or <code>ORA-22924: snapshot too old</code> These indicate whether the problem is related to a rollback segment or a LOB access. 2. Verify Query Duration vs. UNDO_RETENTION If the query duration (shown in the log, e.g., Query Duration=1974 sec ) exceeds UNDO_RETENTION , increase UNDO_RETENTION and consider enlarging the UNDO tablespace. If the duration is short, review MOS documents such as Document 1131474.1 for edge cases. 3. Inspect UNDO Data Files Ensure all UNDO data files have auto‑extend enabled with a MAXSIZE clause. Mixing auto‑extend and fixed‑size files can corrupt the TUNED_UNDORETENTION calculation. 4. Check TUNED_UNDORETENTION Compare TUNED_UNDORETENTION with MAXQUERYLEN : If TUNED_UNDORETENTION is smaller, the UNDO tablespace is under pressure; increase its size. If it is much larger, it usually means UNDO files lack auto‑extend; enable auto‑extend with MAXSIZE to reduce the retention value. 5. Adjust UNDO_RETENTION Set UNDO_RETENTION to at least half of MAXQUERYLEN . Larger values help avoid the error but may increase UNDO space usage. Additional Diagnostics High proportions of ACTIVE/UNEXPIRED UNDO can also cause the error. Causes include: Very large UNDO_RETENTION or TUNED_UNDORETENTION values. Massive UNDO generation at a specific time (use the provided SQL to identify heavy queries). Large numbers of dead‑transaction rollbacks. Use of Flashback Query. Refer to Oracle MOS Document 1337335.1 for scripts to check active UNDO segment usage. Summary ORA-01555 arises when the required UNDO snapshot is no longer available, either because it expired, was overwritten, or LOB consistency limits were exceeded. By enabling trace logging, reviewing alert/trace files, aligning UNDO_RETENTION with query lengths, ensuring UNDO tablespaces are properly sized and auto‑extend enabled, and monitoring active UNDO usage, you can reliably diagnose and prevent this error.