Snapshot Too Old Error

Snap-Shot too Old Error

Reasons

Snapshot too old error is occur whenever any query is fired and it does not get consistent data or consistent view from and undo tablespace then snapshot too old error comes.

Scenarios. For example

i)         when hr user updates all rows to 50gb table. then it generates undo data. and no commit given yet.

ii)           At the mean time scott user want to access all rows of the table then it gives result from undo tablespace.

iii)   Then again hr user commits all updates transactions so here all commited transactions are deleted from undo tablespace.

iv)          So here scott user that fires long-running query receives snapshot too old error because now there is no remaining data into the undo tablespace.

Solutions

-> configure an appropriate undo retention interval

UNDO_RETENTION:- parameter specifies the how long already committed transaction are to be retained into the undo tablespace.

-> size of the undo tablespace properly

-> Consider guaranteeing undo retention

Guranteeing undo retention

The default behavior is overwrite the committed transactions that have not yet expired rather than to allow an active transaction to fail because of the lack of undo tablespace.

This behavior can be changed by guaranteeing undo retention, undo retention settings are enforced even if they cause transactions to fail.

Retention Gurantee can only applied to undo tablespace, cannot given to any other tablespace.

what is the work of Undo Advisor ?

it provides an estimate of the undo tablespace size required to satisfy a given undo retention.