ORA-01555: Snapshot too old - Error in Oracle

Some ORA errors are easy to understand for common user..Some are "technical"..This post aims to help a beginner to understand what this snapshot too old error is about and what are the common causes for this error. I will try to keep this as simple as possible so that it can be easily understood.

Before we understand much about this error, we have to understand a few jargons which we will be mixing up in due course when this problem is described in more detail..

Read Consistency: Oracle always enforces a statement level read consistency. Hey wait..it is getting tricky here. What this means is, the output data that is returned due the execution of a SQL query will be consistent with respect to time when the query began..This means, while the query is getting executed, the query does not see the changes made to the underlying data by transactions that COMMIT during the execution..I hope we have got a fair bit of idea on what read consistency is all about now. We can move on.

SCN: System Change Number..A given point in time => How is this identified by Oracle? Oracle uniquely identifies a given point in time, by using a set of numbers called the System Change Number (SCN).  So, it can be said that SCN helps to identify the state of the database at one given point in time.

How Oracle Achieves Read Consistency: We will try to relate Read Consistency with SCN. When a query enters the execution phase, Oracle marks the "SCN" at that point in time. Now, the Query that is getting executed can only see a "snapshot" (note the word, we are slowly getting into the error code !) of the rows, as they were at the time the SCN was marked..

So, far so good. We have seen what Read Consistency is all about, what SCN is to a basic level and also understood how SCN helps Oracle to achieve "Read Consistency".

Now, we introduce one more => Rollback segment..We need to understand this, as Oracle uses this to achieve Read Consistency.

When a transaction (update, for example), makes any changes to a row(s), a snapshot of the record before applying the changes requested by the transaction, is copied to an area termed as "rollback segment". This can be useful is the transaction issues a ROLLBACK which would help in restoring the row(s) back to the original state. Now, this rollback segment area has got an address which is stored in the in the header area of the row that got updated. This header area (data block) also contains something we discussed =>SCN, which has the value of the last COMMITTED change to that row.

When a query gets executed, and starts reading the rows (data blocks), the SCN of the query at the time of execution is compared with the SCN stored in the header of the block. If the SCN in the header of the block is less than the SCN of the query, then the data would be read directly. Otherwise, it means that the data has changed..(new SCN / or UNCOMMITTED changes to the data block). This would also mean that the query has to look "elsewhere" to provide a consistent read of rows at the time of execution. This reconstruction of the data is done using the saved snapshot that was done into the rollback segments.

In a scenario, when Oracle is not able to reconstruct the snapshot for a long running query, we end up with an error ORA 01555..Snapshot too old..(to reconstruct)

This results in a question? What happened to the saved snapshot in the rollback segment?

Rollback segment maintains a snapshot of the data only when the transaction that created the snapshot is active..ideally meaning that a COMMIT or ROLLBACK is not issued by the transaction. If a COMMIT / ROLLBACK is issued, then Oracle will try to reuse the space allocated for that snapshot..(as this is no longer required due to a COMMIT / ROLLBACK)..

Now, assume that a query is running for a long time, now attempts to read this area in rollback segment, but unfortunately, this area is no longer current due to a reuse..(overrite by some other transaction)..what gets thrown in these cases is ORA 01555…

We are now ready to move to the next part of this post..What can be the cases in which ORA 01555 can occur? Continue reading here..

Share your thoughts, do you find this useful?