Oracle holds undo information in case you need to roll back a transaction and also to keep a read-consistent version of data. Long-running queries may need the read-consistent versions of the data in undo segments because they may not be at the same System Change Number (SCN) as the ones currently in memory. (They may have been changed since the start of the query.) If the undo segment holding the original data is overwritten, the user receives the dreaded Snapshot Too Old error. With advances in Oracle 11g, this error is, indeed, not rare (using automatic undo management), but there is another, more frequent occurrence of the error in the later versions of Oracle.
In their infinite wisdom, developers find wonderful ways to update information that they are querying within the same piece of code causing this problem. They are the ones both querying and updating and causing the Snapshot Too Old error to occur. One flawed developer method is known as the Fetch Across Commit. In this method, the developer first selects a large number of rows from a table into a cursor and then fetches the rows to use for an update to the table, committing after a select number (say, every 1000 records) based on a counter. What happens is that the cursor needs a read-consistent image of the table, yet the developer is committing 1000 records within the same code to the table. The result is a Snapshot Too Old error.
NOTE
See an excellent paper by Dave Wotton on understanding Snapshot Too Old for a detailed explanation of this esoteric problem. This doesn’t happen as much as it used to.
TIP
In addition to the more typical reasons, when developers modify the data as it is being selected, fetching across commits, the Snapshot Too Old error can occur. To fix this problem, close and reopen the cursor causing the issue.
Leave a Reply