Snapshot too old after 11 extents

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 Received on Sat Sep 23 2000 - 11:08:55 CDT