Re: Optimal Rollback Segment Size Question

In article <mlcampb1.2.000AE878_at_ingr.com>, mlcampb1_at_ingr.com (Mike Campbell) writes:
> I know that in Oracle 7 rollback segments can shrink back to a pre-defined
> optimal size, but I need to know how to make this work.
>
> For example, I have a rollback segment that currently as 13 extents allocated
> using approx. 2.8M of space. If I set the optimal size to 200K, how do I force
> Oracle to shrink this rollback segment.
>
> I went into SQL*Plus and did a 'set transaction use rollback segment r01' and
> then issued a delete from a table to delete 28 rows and rolled back that
> transaction. When I went back into monitor rollback it still showed that
> segment rc01 was using 2.8M. How do I make it shrink???
>
> Do I need to issue a transaction that is larger that the optimal size or even
> larger than the currently allocated size??
>
> Any suggestions
>
> Mike Campbell
> mlcampb1_at_ingr.com

ORACLE shrinks the rollback segment when it moves to a new extent in that segment. If the total size of the segment is > than the optimal size, and the next extent is not being used by any transaction, it will free up the extent. It will continue to do that while if finds free extents, and while the total size is > optimal.

So, to force a shrink, you have to force enough activity to have ORACLE move to a new extent within the rollback segment. Not that I'm an expert, but it has seemed to me that DELETEs don't generate massive redo activity. I'd try a large delete.

Be careful. If anyone has a transaction in the next extent, you will actually make the situation worse because ORACLE will expand the rollback segment.

You could always drop and re-add the segment in question.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285