Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to shrink undo space?

Re: How to shrink undo space?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 6 Feb 2003 06:53:53 +1100
Message-ID: <IQd0a.41546$jM5.104758@newsfeeds.bigpond.com>

"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
Received on Wed Feb 05 2003 - 13:53:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US