Re: understanding rollback segments

On 25 Apr 1997 10:22:31 GMT, Dave Wotton <Dave.Wotton_at_it.camcnty.gov.uk> wrote:
>thanks for replies so far about why I appear to need a big rollback
>segment when copying a large table using PL/SQL, even when I'm
>committing my inserts after ever 1000 records. I summarise below:
>
> [ snip ]
>---------------------------------------------------------------------
>
> Ed Poorbaugh <poorbaugh_at_norcross.mcs.slb.com> says:
>
>I think that the rollback maintains the consistency of the cursor so
>that your cursor select statement "sees" the database as it existed at
>the moment you opened the cursor. Since your cursor will not be closed
>until the end of your loop this keeps the rollback active and things
>tend to grow too large.
>
>Me: Ok, but the open cursor is for a select against data which is
> not changing - I'm inserting into a completely different table,
> and I've got no open selects on that.
>
>------------------------------------------------------------------
> [ snip ]

Dave,

Here is an extract from Bulletin 99267.6 which I got off an old copy of the Support Notes CD ROM. It's an oldie, but a goodie.

"When the database needs to enter the next extent e in s, in addition to checking the status of e, the statuses of all other rollback segments are also checked. Extent e will not be entered if there is an active update transaction t in another rollback segment that began before the oldest (committed) transaction in e. Extent e will not be entered because entering e will remove information that could potentially be required for giving t a read-consistent view of data."

It may be that because you still have a cursor open from your initial transaction, it is still regarded as "active". If so, the behaviour you have observed can be explained in terms of the "potential" dependency of the open cursor on old rolback data, regardless of the fact that there is no DML against that table. Oracle will just keep extending all rollback segments as long the cursor is open, so that it does not overwrite any rollback data newer than the read consistent SCN for cursor.

This however cannot be the whole story. Because if it were, it would not be possible for such cursors to ever get the ORA-1555 error (snapshot too old). Also, active DML statements would never get snapshot too old either. But I am struggling with an insert .. select statement in the middle of a transaction that gets them anyway under 7.3.2.3. So maybe this functionality works at the statement level, rather than the transaction level. It would be nice to know! Received on Sun Apr 27 1997 - 00:00:00 CDT