Re: Snapshot too old after 11 extents

Your problem is the fact that you commit every 100 rows. That marks the rollback blocks for those 100 rows as over-writeable, even though your cursor still needs them.

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Leonard F Clark" <lfc_at_zoom.co.uk> wrote in message
news: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?

>

> Len