Re: shrinking rbs?

A copy of this was sent to johnt_at_tman.dnsalias.com (if that email address didn't require changing) On Fri, 01 Jun 2001 19:26:08 GMT, you wrote:

>Doug O'Leary <dkoleary_at_ro05-24-29-232-217.ce.mediaone.net> wrote:
>> Thomas Kyte <tkyte_at_us.oracle.com> wrote:
 

>>> The problem is a query doesn't "USE" rbs. It might need the contents of
>>> rbs at some point to provide a read consistent view of the data -- but
>>> it is not using rbs. A "write" transaction will use RBS and if it was
>>> using it -- we would not shrink it.
 

>> That's the concept that I was missing - and explains the risk. Thanks
>
>But if there is no 'task' using the rbs, wouldn't a read-consistent view
>already be in the table itself? Therefore, by shrinking a RBS I can't
>see how you can cause a problem with any query.

Correct, if no one was running any queries (inserts/updates/deletes all count as queries here -- they need a read consistent view of the base table as well) -- then the odds of an ORA-1555 or pretty much not there. Here is one case where a 1555 might occur given this scenario:

the example has us keeping 2 extents out of many (probably 121). we'll discard 119 of them...

In the beginning at time T1 a query begins.

At T2, an update starts and writes to RBS extent #3. It happens to modify the 'bottom' of the table the above mentioned query is reading.

At T3, the query commits -- this will allow the data in extent #3 to possibly be overwritten.

At T4, other activity is taking place, the rollback segment advances from extent #3, to #4, to #5

At T5, the DBA shrinks the rbs. Extent #3 is one of the casulties. It is gone.

At T6 the query discovers it needs to rollback the block at the "bottom" of the table it was reading -- but it cannot since that rollback data was in extent #3.

Now, since shrinking a rbs will throw away the OLDEST extents first (in this example #4 and #5 are the "newest") -- this usually wouldn't be a problem except in the extreme case (as this is, going to 2 extents) as the older the rbs data, the less likely we'll need it.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp