Re: Shrinking Rollback Segemnts in a Proc

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 :

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