Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: snapshot to old again

Re: snapshot to old again

From: Kevin A Lewis <KevinALewis_at_Hotmail.com>
Date: Thu, 20 May 1999 10:18:54 +0100
Message-ID: <dnQ03.33$kF4.51@news-reader.bt.net>


Basically your understanding I think is correct

Read consistent is all about comparisons of SCN (system change numbers) if all the data in the read is marked with numbers smaller (earlier) than the start of the read then Oracle reads from the database object. If the SCN is greater Oracle seeks to find a 'Read Consistent Image' from Rollback. It needs to find the before image and then if this has an smaller (earlier) SCN everything is OK. If it cannot find an image older another transaction will have overwritted the Rollback block and 1555 is the result.

Solution is run the routine on it's own  or recode to break up the read
 or add more rollback segments (better for OLTP systems)  or increase size of rolback segments (better for DSS or Reporting system/transaction)

Well that's my understanding anyway? anyone else like a go??

Not sure on point 1 of the quote maybe someone else could field that !!!

Regards
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Doug Cowles <dcowles_at_bigfoot.com> wrote in message news:37433BD0.74CD72F1_at_bigfoot.com...
> I'm sure this topic has been beaten to death on this group but hopefully
> a few people won't mind beating it some more. I created a very large 1G
> rollback segment for a very large data load and the writter of the code
> received a snapshot too old message after about half
> an hour. The high water mark on the rollback segment was 300M. (It had
> unlimited extents). So, to my understanding, this can only mean that
> the data load code (2 pieces of code were kicked off at once),
> referenced the same table from two different procedures.
> My comprehension is as follows.
> a) no read locks in oracle
> so -
> b) transaction 1 begins and starts a read-consistent rollback image of
> how every many gazillion rows there are.
> c) transaction 2 also (smaller transaction) begins on to do the same
> thing on a smaller scale, but because of a lock, it waits for the first
> transaction to finish.
> d) transaction 1 commits, thereby rendering the "before" image in
> transaction 2's rollback
> extents futile.
> e) snapshot error.
>
> Is this correct?
>
> Secondly, if someone has the time - this is a quote from an Oracle book
> handed out in their DBA class.
>
> <<begin quote>>
> If the Oracle server cannot construct a read-consistent image of data,
> the user will receive an ORA-01555 SNAPSHOT TO OLD error. This error
> can occur when the transaction that made the change has already commited
> and:
>
> 1) The transaction slot in the rollback header has been reused
> 2) The before-image in the rollback segment has been overwritten by
> another transaction
> <<end quote>>
>
> If my understanding above is correct, I assume that #2 applies, in that
> transaction 2's redo image is not literally overwritten but something
> indicates that is is now out of synch.
> Is this correct?
>
> And finally, what causes #1? What would cause a transaction slot in the
> rollback header to be re-used inappropriately I guess. I don't quite
> follow #1.
>
> Have at it folks
> I really appreciate this group. There are many of you out there who are
> EXTREMELY
> helpful.
>
> - Dc.
>
Received on Thu May 20 1999 - 04:18:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US