Re: Shrinking Rollback Segemnts in a Proc

Kenneth Koenraadt wrote in message news:<3f7c6f06.1274202_at_news.inet.tele.dk>...
> 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 :

Why do you say that? Optimal means ora-1555, in 8 anyways.

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

jg

--
@home.com is bogus.
Lamo FBI: http://www.signonsandiego.com/news/computing/20031001-1510-fbi-reporters.html