Re: How to shrink undo space?

"Howard J. Rogers" wrote:

> "Roger Redford" <dba_222_at_yahoo.com> wrote in message
> news:a8c29269.0302050827.c1ff936_at_posting.google.com...
> > Dear experts,
> >
> >
> > I'm discovering that 9i is a lot different than 8i.
>
> True enough. :-)
>
> > The 9i database I inherited had the undo tablespace's datafiles
> > set to autoextend. This is turned off now. But, the
> > unix file system is now at 99%, and this is causing alerts.
>
> I could be facetious and suggest that the real issue here is a lack of disk
> space, not that your undo tablespace has grown (presumably for some reason,
> such as "it needed to, to accommodate the amount of undo that was generated
> in the database").
>
> > In 8i, if the rollback tablespace was too big, for each
> > rollback segment, you could just:
> > alter rollback segment x shrink
> > You could then resize the datafiles smaller.
>
> Although you *could* do this in 8i, it was bad practice to do so, since
> shrinks mean your rollback segments get smaller, and therefore you increase
> the risk of them needing to grow again.
>
> > But, in 9i, with undo, I get:
> >
> > ERROR at line 1:
> > ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
>
> Strangely enough, the idea of 'automatic' undo is that its administration
> is, er, automated. So yes, commands such as these fail.
>
> >
> > If the parameter, undo_management, is set to MANUAL, is it
> > possible to shrink use the shrink command, and then resize
> > the datafiles?
>
> Alll things are possible, but one questions whether they are appropriate.
> Shrinking things that have a grown for a reason is not sensible. But if you
> are desperate to get some free disk space whilst waiting for your
> newly-purchased disk space to arrive, then set the thing to MANUAL, bounce
> your instance, drop the tablespace entirely, and re-create it, switch back
> to AUTO, and have a final instance bounce.
>
> HJR
>
> >
> >
> >
> > Thanks

I agree with your basic premise ... if Oracle used it ... it do so for a reason. But one thing we both forgot to mention is that the OP should look at the current retention time. Perhaps it is longer than required.

Daniel Morgan Received on Wed Feb 05 2003 - 18:52:26 CST