Cause for ORA-01555 - Snapshot too Old

In our previous post, we discussed the basics of ORA 01555 - Snapshot too Old error. In this post, we will understand the common causes of this error and some reasons on why this error happens and what can be done to prevent this error. It is highly recommended to read the previous post before reading the reasons for this error, as this would help the reader to understand the error in much detail.

1) Highly happening Database, Few / Smaller Rollback Segments

Earlier, we discussed how rollback segments play an  important factor for this error. Going by what we understood, if an Oracle database is very active in the sense that there happens a huge number of COMMITS, then the possibility for the space left by a snapshot to be reused in a much shorter span of time. This would mean that this left our space can get overwritten very quickly as the rollback segments are small. So, a larger rollback segment space in this scenario, would not erase the data as often and can reduce the chance of this error. A long running query will also be able to reconstruct the data from the snapshot as the probability for the snapshot to remain is increased due to the increase of rollback segment space.

2)  Bad or Corrupted Rollback Segment

If the rollback segment is corrupted or could not be read due to any reason, then a long running query attempting to perform a reconstruction will result in this error. As this is very evident here, due to corruption, the query will not be able to reconstruct the snapshot. Have you ever experienced this reason? Share what happened and how you fixed it.

3) Fetching and COMMITTING data onto the same table

This is case, when a query opens a cursor on a table, loops through it fetching  data from the table, changing it, and COMMITing data on the table in the process over and again. When this happens, at times if the query attempts to reconstruct data for a row (that had an updated SCN), it will attempt to reconstruct the record from the snapshot in rollback segment. And if this information cannot be obtained from the rollback segment due to the reasons explained in (1), it will result in ORA 01555 error. It is to be noted that this is one of the most common reason for snapshot too old error. To prevent this kind of error, it is advised to COMMIT less often in these kind of scenarios. When done this way, the rollback segment tend to get larger and will reduce the probability of getting snapshot too old error.

It is to be noted that the ANSI standard does not allow this to happen, but Oracle allows users to fetch across COMMITS. ( I read this in a Metalink note..Do you have anything to add to this? )…

Do you know any other reason for this error? Post it in the comments section..