Guest

• Dec 29th, 2005

 

A select query may fail with a 'snapshot too old' error in the following situation:

1. A user issues a long running select query.
2. Another user starts a transaction which updates the same table used in the query
• the pre-update image of the data is held in rollback segment till the transaction is committed or rolled back.
3. The user commits the transaction.
• Now the data buffer contains modified data which is not read consistent for the select query since it was issued before the data was changed.
• The rollback segment entry containing the pre-update image is not active since the transaction is complete.
4. If the rollback segment entry is overwritten by another transaction, the query issued in step (1) will fail, since it is unable to retrieve read consistent data.
5. This situation may be corrected by choosing a bigger size for the rollback segment and making all rollback segments of that big equal size. This is because in rollback segment it is always the oldest entry which is overwritten by new transactions.

The DML queries may fail in case there are large number of active long running transactions, resulting in filling up of all rollback segments with rollback entries which must be kept till the transactions are committed or rolled back. In such a case Oracle does not allow more DML statements to be executed.