Diagnosing and Resolving ORA-01555 Snapshot Too Old Errors

The Problem: Data Vanishing During Long Reads

An ORA-01555: snapshot too old error occurs when a long-running query needs to see a consistent version of data from the moment it started, but the required "undo" data (the previous versions of modified blocks) has already been overwritten by newer transactions. This is not a data corruption issue, but a resource contention problem between long-running reads and high-volume writes.

The key takeaway: To stop ORA-01555, you must ensure that the Undo Tablespace is large enough and the Undo Retention period is long enough to cover your longest-running query while concurrent updates are occurring.

Quick Diagnostic Matrix

Symptom	Likely Cause	Primary Diagnostic Check
Error occurs consistently at the same time in a batch job.	UNDO_RETENTION is lower than query duration.	SHOW PARAMETER undo_retention
Error occurs randomly during peak DML loads.	Undo tablespace is too small; space is being reused prematurely.	V$UNDOSTAT (UNEXPSTEALCNT)
Error occurs on a small table with frequent updates.	Delayed block cleanout or high-frequency commits.	Check commit frequency in application logs.

Step-by-Step Diagnostic Workflow

Run these checks as a user with SYSDBA or SELECT ANY DICTIONARY privileges on the database instance.

1. Quantify the Error Frequency

First, determine if this is a systemic issue or an isolated incident by querying the Undo statistics view. Run this on the server via SQL*Plus or SQL Developer:

SELECT begin_time, end_time, ssolderrcnt
FROM v$undostat
WHERE ssolderrcnt > 0
ORDER BY begin_time DESC;

The ssolderrcnt column tracks the number of ORA-01555 errors that occurred during that time slice. If this count is high during your batch window, the undo configuration is insufficient.

2. Compare Retention vs. Execution Time

Check your current retention setting:

SHOW PARAMETER undo_retention;

Compare this value (in seconds) to the actual runtime of the failing query. If the query takes 4 hours (14,400 seconds) but undo_retention is set to 900 (15 minutes), the database is permitted to overwrite the undo data long before the query finishes.

3. Check for "Stolen" Undo Space

Even if undo_retention is high, Oracle may overwrite unexpired undo data if the physical tablespace is full. Check for "stolen" blocks:

SELECT max(unexpstealcnt) FROM v$undostat;

If unexpstealcnt is greater than 0, it means the database was forced to overwrite undo data that had not yet reached its retention age because there was no other space available.

Implementing the Fixes

Scenario A: Retention is too low

If the query duration exceeds the retention parameter, increase the limit. For example, to set retention to 8 hours:

ALTER SYSTEM SET undo_retention = 28800 SCOPE=BOTH;

Scenario B: Tablespace is too small

Increasing the parameter alone does nothing if the disk space is capped. You must ensure the Undo tablespace can grow to accommodate the retention period. Check the datafile status:

SELECT file_name, bytes, autoextensible 
FROM dba_data_files 
WHERE tablespace_name = 'UNDOTBS1';

If autoextensible is 'NO', enable it or add a new datafile to the tablespace.

Scenario C: Critical Reporting Requirements

If you cannot afford an ORA-01555 under any circumstances, use Guaranteed Retention. This forces Oracle to honor the undo_retention period regardless of space pressure.

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

Risk Warning: With RETENTION GUARANTEE, if the undo tablespace fills up, DML operations (INSERT/UPDATE) will fail with an "out of space" error rather than overwriting old undo data. Monitor disk space closely after enabling this.

Verification and Rollback

Verification: Rerun the failing query during a period of active DML. Query v$undostat again to ensure ssolderrcnt remains at 0 for the duration of the job.

Rollback: To revert the retention or guarantee settings:

• To reset retention: ALTER SYSTEM SET undo_retention = [original_value];
• To disable guarantee: ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;