RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-...
The SNAPSHOT_TOO_OLD erro will come ... the query will goves into cartesion product or infinite loop .
Other case is suppose ur updated loarge no of rows at atime without saveing the records . In this case u can use commit statement for every 500 records then u can avoid this problam. Or ask DBA to extend the table space for this segment.
RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-...
Snapshot_too_old exception is thrown when ur performing very large DML operation without commiting, this can be resolved by increasing undo retention period. contact ur DBA to check for Undo retention period
RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-...
when switch on to one undo tablespace to another that time for prevoius currently runnig tables the data will be not available so this time also his error will occur.for rectify this error make undo tablespace size large.to avoid this error set undo_suppress_error=true
RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-...
There may two Cause for SNAPSHOT-TOO-OLD error:-
(1) Since space managemnt in undo segment follo LRU algorihm.So in space allocated for to undo segment will so small to data may frequently swaped out from undo segement and may cause SNAPSHOT-TOO-OLD error.
(2) It also depend on the undo retention policy set by the DBA.If the undo retention time is set to very low the the data will sweped out too early and may cause SNAPSHOT-TOO-OLD error.
RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-...
When any DML operation is taking long time which may lead reuse of datablocks of undo segment by another DML, in this case oracle will throw snapshot too old error.
For any DML oracle stores the undo image in rollback segment, suppose transaction A is in progress which cause datablock from undo segment been used by this transaction.
And now if another transaction B is started which need datablocks from undo segment to keep undo copy, but if rollback segment is not large enough to provide the datablocks for second transaction B, then transaction B will use the datablock which are already in use to retain the undo image for transaction A, and this will lead to snap shot too old.
Simple solution change the commit point by chaning checkpoint frequency. And if it's possible then increase undo segment size by adding datafile to undo tablespace.
