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.
RE: what is SNAPSHOT-TOO-OLD error? How does snapshot-too-old error can be rectified in Oracle 9i?
First you have to understand that Oracle is a MVCC (multiversion concurrency control) or repeatable read database system. Queries are returned with all rown consistent with respect to the point in time (SCN) you bagan the query (or earlier with flashback query ). Oracle accomplishes this by examining each block as you query it. If a block has had a transaction started on it by another session since your query SCN your query refers to the UNDO to determine if a prior version of the block needs to be reconstructed in cache to fulfill your consistent read.
ORA-01555 (Snapshot too old) is simply the error returned when your read-consistent query (not necessarily a transaction) cannot find the necessary UNDO to reconstruct the way a block looked when you began the query. This occurs because other users have been using the UNDO and the necessary entries have been overwritten by other users performing DML. Often tis happens when your query has been running for a very long time (poorly-tuned query). Therefore the first question to someone complaining about OR-01555 should be How long did your query run before you get the error?
There are two cases: WITH the UNDO_TABLESPACE specified (Automatic Undo Management) and WITHOUT the UNDO_TABLESPACE (old-fashioned rollback segments).
With UNDO_TABLESPACE: You may get ORA-01555 when your query runs for longer than the UNDO_RETENTION parameter. You can either tune the query or increase UNDO_RETENTION. It is also possible that the size of the UNDO_TABLESPACE may need to be increased to fulfull the needs of the UNDO_RETENTION parameter.
With old-fashioned rolback segments: When other users performing transactions use the rollback segments enough that the entries needed to reconstruct a read-consistent view for you have been overwritten you receive ORA-01555.
In all cases the first solution is to make the query run faster so that the chance of needed a very old block version is reduced. Only if that is impossible should you consider increasing UNDO_RETENTION or increasing the size of the UNDO.
Jeremiah Wilton Blue Gecko Inc. Remote Database Services bluegecko . net
