ORA 01555–Snapshot too Old–Rollback Information Overwritten

In this post, we will attempt to illustrate in detail, one of the two main reasons for Snapshot too Old error. It is advised to read about delayed block cleanout, as we will be making use of this terminology in this post.

Scenario 1 – Rollback Information is Overwritten and is no longer available

For illustration purposes, we will assume that a session overwrites the rollback information it requires resulting in this error. To understand how this results in ORA 01555, consider the following sequence of events:

1. Session A executes a Query at time T1 . The SCN is 100.
2. Session A selects a Block B1 during this Query
3. Session A does an update on Block B1. The SCN becomes 101.
4. Session A updates some other tables, generating some more rollback information.
5. Session A issues a COMMIT for the updates made in Step 3 and Step 4. This would mean that other transactions are free to overwrite the rollback information generated due to the updates performed by Session A.
6. Session A selects a different data from Block B1.

At this point the header of Block B1, will have a SCN that is different from the SCN value that was during the start of the query. (i.e. 100 in our case). This would mean that Oracle, to maintain read consistent information, will now have to get the block image when the Query was executed. (i.e.. image of the block as of SCN 100). This is depicted below

Rollback Information Overwritten - Scenario

If Oracle can find a older version of the block in the Buffer Cache, then it can be used. Otherwise, the current block needs to be ROLLBACKed sufficient enough to get an image as of SCN 100.

In such a case, if Oracle is not able to get the rollback information that it is after (Session A has generated quite a lot of rollback information which could have overwritten the data that Oracle is looking for), we get ORA 01555 – Snapshot too old error. In the next post, we will discuss one more reason for this error – rollback transaction slot getting overwritten.

Have you ever got this issue? Tell us what you did in the comments section.