Re: Shrinking Rollback Segemnts in a Proc

Hi Howard,

I am certainly not in favor of "optimal", it's like sand in the shoes.....

But it should not be necessary to shrink them (manually or automatically) on a regular basis. The ideal is to let them grow as necessary and leave them alone, I think.

And maybe, twice a year, you could shrink them manually, when an application goes crazy, generating 16 Gb of undo space.....shrinking them on a regular basis (e.g every night) would hide such symptoms of crazy applications. That was what I meant.

• Kenneth Koenraadt

On Fri, 03 Oct 2003 06:35:50 +1000, "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>Kenneth Koenraadt wrote:
>
>> On Thu, 2 Oct 2003 11:10:56 -0400, "paddy_nyr" <mpprpp_at_yahoo.com>
>> wrote:
>>
>>>I'm trying to curosr through the dba_rollback_segs, so I can shrink my
>>>rollback segements. Can this be done through a procedure and does anyone
>>>have some sample code? I'm running Oracle 8.1.7.
>>>
>>>Many Thanks
>>>
>>>
>>
>> If you need to shrink your rbs' manually, your DB is almost certainly
>> wrongly configured, but here goes :
>
>
>That's an incorrect statement. If he needs to shrink them manually, then
>he's almost certainly doing an excellent job of precisely managing his
>database.
>
>Quite possibly he feels that setting something moronic like 'optimal' is not
>particularly good for performance, because the related automatic shrink
>happens in the middle of transactions, as they are crossing the extent
>boundary. Much better, he probably (and rightly) feels, to take manual
>control of the shrinkage process; live with enlarged rollback segments for
>a short while, and at a time and place of his choosing, not in the middle
>of transactions, shrink them back into shape.
>
>So his database is almost certainly configured rather well, I would have
>thought.
>
>Regards
>HJR
>
>
>
>
>
>
>>
>> create or replace procedure shrink_us authid current_user
>> is
>> begin
>>
>> for c_rec in (select 'alter rollback segment ' ||
>> segment_name ||
>> ' shrink to 4m' as shrink_statement
>> from dba_rollback_segs
>> where segment_name != 'SYSTEM') loop
>>
>> execute immediate c_rec.shrink_statement;
>> end loop;
>>
>> end shrink_us;
>> /
>>
>> This won't work in 9i if you run Automatic Undo mode.
>>
>>
>> - Kenneth Koenraadt
>
Received on Fri Oct 03 2003 - 12:09:52 CDT