Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Shrinking Rollback Segemnts in a Proc

Re: Shrinking Rollback Segemnts in a Proc

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 03 Oct 2003 06:35:50 +1000
Message-ID: <3f7c8ce2$0$2484$afc38c87@news.optusnet.com.au>


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 Thu Oct 02 2003 - 15:35:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US