Re: Snapshot too old

Are you sure it was recommended by Oracle? Error Snapshot too Old has nothing to do with redo logs. It is rollback segment issue. Data fetched from a SELECT is always time-consistent. It means Oracle always returns data the way it was at the time SELECT statement was issued. At the same time your or some other Oracle session can change this data and commit the changes. This forces Oracle to look for time-consistent version of data in a rollback segment of the transaction that made the changes. Because the changes are already committed, the corresponding portion of a rollback segment is released. If it is assigned to an another transaction, the time-consistent version of data needed by your SELECT is lost and SELECT fails with Snapshot too Old error. To resolve such situation you have to find out is it your session that makes changes to SELECT data. If it is some other session, you have a: a) Schedulling issue. Simply do not schedule these two jobs at the same time. b) Application issue. Two people running the same application and it is perfectly valid. Then it should be resolved on application logic level (e.g. by using locks). If the changes are made by your session you should estimate rollback segment size, create such a segment (if there is no rollback segment that fits your size) and issue SET TRANSACTION USE ROLLBACK SEGMENT xxx; as the very first statement in your transaction.

Solomon Yakobson.

In article <63gfti$pt0$1_at_news.interlog.com>,   reberhard_at_interlog.com (Roberto Eberhardt) wrote:
>
> We constantly run into the ORA1555 error (Snapshot too old), to remedy this
> Oracle recommends increasing the size of the redo logs.
>
> This can be accomplished in two ways:
> 1- Increase the size of individual segments
> or
> 2- Increment the number of segments.
>
> Any suggestions on which one would be the most effective solution?

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet