ORA-01555: How to Preserve Undo Data and Keep Queries Consistent

Oracle database has many problems and often recurring ORA-01555 is one of them. It is known as the “snapshot too old” error, and occurs when Oracle cannot provide a consistent read of the data that existed at the query’s start time. Oracle attempts to use undo data to roll back any changes made after the query began. If that undo information has been overwritten or otherwise lost, Oracle can no longer reconstruct its older version. This leads to the ORA-01555 exception.

Primary Scenarios Leading to ORA-01555

1. Earlier Large DML Transaction (Delayed Block Cleanout)
   • A previous, large data manipulation language (DML) transaction, such as a massive UPDATE, INSERT, or DELETE, can leave behind “dirty” blocks.
   • Oracle delays cleaning out these blocks until “something” revisits them. If the blocks are cleaned out while another long-running query needs them as they appeared at the start of the query, a read-consistency failure can occur.
   • In many cases, the query producing ORA-01555 is doing a full table scan on data that was heavily modified in the recent past. The delayed cleanout may force Oracle to access undo data that no longer exists, triggering the error.
2. Insufficient Undo or Retention Settings
   • If undo space is not large enough or the UNDO_RETENTION parameter is set too low, older undo blocks are overwritten more quickly.
   • A long-running query that spans a large number of changes requires enough undo records to rebuild the consistent snapshot of all needed blocks. Overwritten undo means Oracle cannot reconstruct the older versions of those blocks.
3. Concurrent DML During a Query
   • Data blocks being changed (committed or uncommitted) while a lengthy query is reading those same blocks can prompt Oracle to look for undo data for a large number of recent changes.
   • In a well-configured masking or extraction job, no other transactions should change data at the same time. If they do, the undo usage can spike, increasing the risk of ORA-01555.

Strategies to Resolve or Prevent ORA-01555

When ORA-01555 appears, Oracle can no longer retrieve the data as it looked when the query started. The following methods address the primary causes of this error, focusing on ensuring your queries always have consistent, undo-protected data.

Perform Delayed Block Cleanout

A large DML transaction that occurred recently can leave partially cleaned data blocks. To avoid encountering ORA-01555 during a subsequent query, run a “touch” query that forces a full table scan and triggers block cleanout before the main operation. For example:

ALTER SESSION SET events ‘10949 trace name context forever, level 1’;

SELECT COUNT(*) FROM (SELECT ROWID FROM [TABLE_WITH_ORA-01555]);

Repeat the same step for each affected table, ensuring a comprehensive sweep of all relevant blocks. Once read into memory, these blocks undergo final cleanup, eliminating the risk of an unexpected read-consistency error.

Increase Undo Tablespace Size or Retention

If delayed block cleanout is not the primary factor, consider the possibility of inadequate undo space. Extending the undo tablespace or raising the UNDO_RETENTION parameter makes older undo data available for a longer period, which is critical if queries take substantial time to complete. In environments such as masked virtual databases (vDBs), recreating the VDB or applying a configuration template with higher undo settings can address consistent-read shortcomings.

Avoid Large DML During the Query

Scheduling queries or masking processes during off-peak hours helps prevent concurrent heavy transactions from overwriting critical undo data. In masking scenarios, best practices discourage any changes to the data during the process—updates, inserts, or deletes can all accelerate the undo turnover rate and increase the likelihood of ORA-01555.

Use an Optimal Masking or Extraction Job

An “optimal” job setup reduces unnecessary index-related overhead and ensures sequential block scanning. Where possible, remove or disable indexes on masked columns to minimize additional block writes, and avoid introducing WHERE or ORDER BY clauses that disrupt straightforward table scans. Also, limit frequent commits inside cursor loops; each commit can generate additional undo records that quickly reuse transaction slots. Disabling triggers on affected tables can further minimize unexpected data changes and reduce stress on the undo mechanism.