Re: Shrinking Rollback Segemnts in a Proc

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

But you should _check_ them every night (or better, monitor them online). So you might as well shrink and notify, right? Unless you have an unusually stable operation, you never know when craziness will occur.

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

jg

--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/22.91.html#subj12