HImanshu

• Oct 26th, 2006

 

Many time people come accross "Snapshot too Old" error. there are many reasons for this error but the most basic and important one is that the rollback segments are too small.

Accordingly, this error can arise when a long-running query is being executed at the same time as data manipulation transactions.

Possible Remedies:

• Increase the size of all the roll back segments.

• Add more rollback segments.

• Increase the ?OPTIMAL? size of the roll back segments, i.e.:

Alter rollback segment "XXX" storage (optimal NNNK);

• If the error seems to be associated with a particular application (job or script and not necessarily the one producing the error) then investigate whether that application?s use of rollback space could be reduced. For example, consider whether the application could 'commit' more frequently. For long running applications (particularly if it is the application that is encountering the error) consider whether any record sets could be refreshed periodically rather than relying upon the same record set contents for the entire run of the application.

 

suresha mathada

• Nov 15th, 2006

 

hi,

The snap shot too old can also arise because of LOB.

If we do concurrant writes to same segment then ORACLE will alllow it.

But the data will be corrupted. The subsequent reads will give rise to this error

sujatha dhanesh

• Dec 1st, 2006

 

Yes as U said,

If on one side a query is executed and simultaneously a DML is happening at the same time.  THEN the user doing the DML action gives a commit in between while the query is still executing on the other side then, on the query side U get this message "SNAPSHOT TOO OLD"

Suggestion :

Alter system set UNDO_retention = 900 ;

or give a large value for undo retention, ie., the undo block will be kept in the memory until 900 microseconds even after the commit transaction on the same table, so that the query ends within that.

Mohammed Abdul Afroze

• Mar 25th, 2007

 

Answer:

 Avoiding Snap Shot too old error-(oerr ora 01555).

   First thing here we understood how is the rbs works.This will work in circular fashion by looping around the reuse of the extents which has been released by the commited transactions.Thus in case of long running queries if the data is change while the query is running rbs created for that change for the long running query.When these extents are reused,while these were still needed by the query this error occurs,because oracle can no longer provided a read consistent image of the data.

  To avoid this error.You need to postpone the reuse of the extents.Thus increasing the size of the rbs and the optimal parameter should do the trick.Commiting less often would be another solution.As until transaction is commited rollback segment cannot be reused.And thus reducing the chances of a snapshot too old error.

sanjayoracle

 Profile Answers by sanjayoracle 

• Apr 24th, 2008

 

Just in simple words.....

Ora01555 error occurs when a transation fails when it cannot able to find read consistence image in rollback segment.
 
To solve this problem: Increse the Undo_Retention

Cheers :)