Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Shrinking Rollback Segemnts in a Proc
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 >> >>
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