Difficulty: Medium
Correct Answer: It is a snapshot too old error that occurs when a long running query needs undo information that has already been overwritten
Explanation:
Introduction / Context:
Oracle databases use undo segments to provide read consistency for queries. When a query starts, it should see a consistent view of data as of that start time, even if other transactions are making changes. The ORA-01555 error, commonly known as snapshot too old, occurs when the database can no longer provide this consistent snapshot because the necessary undo information has been overwritten. Understanding this error is important for diagnosing and tuning long running queries and undo configuration.
Given Data / Assumptions:
Concept / Approach:
When a query starts, Oracle notes the System Change Number that represents the point in time for its consistent view. As the query reads blocks, if it encounters blocks that have changed since the query began, it traces back into undo segments to reconstruct the older versions. If, by the time the query needs a particular undo record, that record has been overwritten because the undo space was reused, the database cannot reconstruct the required snapshot. In this situation, Oracle raises ORA-01555 snapshot too old and aborts the query. This is typically associated with long running queries combined with insufficient undo retention or heavy update activity.
Step-by-Step Solution:
Step 1: A query begins and establishes a starting System Change Number for its consistent read view.Step 2: Other sessions continue to update data, generating new undo records that describe how to reverse their changes.Step 3: As the query scans through blocks, it relies on undo records to reconstruct earlier versions of blocks that have changed since the query started.Step 4: If the undo retention setting is too low or undo space is too small, some older undo records may be overwritten and reused for newer transactions.Step 5: When the query eventually needs an overwritten undo record, Oracle cannot build the required older version of the block and raises ORA-01555 snapshot too old.
Verification / Alternative check:
In production systems, ORA-01555 often appears in logs for batch reports or maintenance jobs that scan large tables for a long time. By examining the timing and workload, DBAs frequently see that undo retention was not sufficient for the duration of these queries. After increasing undo tablespace size, adjusting undo retention, or rewriting queries to commit more frequently or process data in smaller chunks, the error usually disappears. This pattern confirms that ORA-01555 is linked to long running queries and overwritten undo information, not to listener failures or syntax errors.
Why Other Options Are Wrong:
Option B attributes the error to a stopped listener, which would produce different network related errors, not ORA-01555. Option C refers to compile time syntax problems in PL or SQL, which result in different error codes such as PLS-00306 or ORA-00900. Option D mentions backup hardware constraints, which might cause media errors but not a snapshot too old condition. These options do not match the established definition of ORA-01555.
Common Pitfalls:
A common pitfall is assuming that ORA-01555 can always be fixed simply by increasing undo retention, without analysing query patterns. Very inefficient queries may still fail even with large undo if they scan data in a way that requires very old undo records. Another mistake is ignoring the error and repeatedly rerunning the same queries without addressing underlying design issues. DBAs should combine adequate undo sizing with query tuning, partitioning strategies, and scheduling adjustments so that long running queries have access to the undo they need for consistent reads.
Final Answer:
Correct answer: It is a snapshot too old error that occurs when a long running query needs undo information that has already been overwritten
Discussion & Comments