Re: Snapshot too old after 11 extents

Leonard F Clark wrote in message <39ccc677.13777320_at_125.0.0.1>...
>Any comments on an error I had recently.
>
>I was running a developer's script (that's my get-out clause: it would
>never have happened to _my_ script, of course :-) and we had a
>snapshot too old error.
>
>I had deliberately varied all but the largest rollback segment off
>line; the large one had next of 5 Mb. There was no other activity
>against the database.
>
>The query was a piece of PL/SQL that was a cursor in a FOR loop with a
>couple of separate inserts in the loop. It was commiting every 100
>rows. The main information is that I was watching the rollback
>segment (via dba_segments) and it was solidly sitting at 11 extents
>(with unlimited maxextents - not my design!)
>
>Why did I get a snapshot too old error when I had loads of Rollback
>Segment space left?
>
>This may reflect the inadequacy of my grasp of rollback segments, but
>I'm puzzled. I know that holding the cursor open (as he was) means
>retaining the rollback segment extents, even though he was committing
>the _inserts_ and I could understand an error that indicated the
>script was removing data the cursor required (I think there _were_
>updates, as well), but why the snapshot too old error?
>
>The only explanation I could come up with is that, because the updates
>within the loop are within the same session as the cursor, the old
>data (before updates) are _not_ retained (i.e. there are no session
>integrity problems because there's only one session). As a result, an
>update has removed data that the cursor needs to use. In other
>circumstances, this would arise from the loss of an old "snapshot" and
>we are getting an error that says one thing but means another (i.e.
>"you just changed the data I needed, you dimwit!")
>
>Am I right, or is there another explanation?

Take a look at my web-page:

    http://home.clara.net/dwotton/dba/snapshot.htm

It should answer all your questions.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.