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??

You probably need to do enough work to make it go into another extent before it will start reducing to the optimal setting.

Alternatively, ALTER the tablespace offline then drop and recreate it with your new settings.

To determine your OPTIMAL size have a look at pages 5 and 6 of Chapter 9 "Managing Rollback Segments" in the section "Set an optimal number of extents for each rollback segment" of the Administrator's Guide. Keep an eye on the average number of 'shrinks' and the average 'active' size. You don't want the database doing 'shrinks' too often but then you don't want your rollback segments to remain too large either.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras