Re: How to shrink undo space?

In article <a8c29269.0302050827.c1ff936_at_posting.google.com>, dba_222_at_yahoo.com says...
>
>Dear experts,
>
>
>I'm discovering that 9i is a lot different than 8i.
>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.
>
>
>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.
>
>
>But, in 9i, with undo, I get:
>
>ERROR at line 1:
>ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
>
>
>If the parameter, undo_management, is set to MANUAL, is it
>possible to shrink use the shrink command, and then resize
>the datafiles?
>
>
>
>Thanks

One would have to ask "why would you want to do this?" In automatic undo mode (AUM), the next time you restart the database you'll have (probably) 10 segments (the formula actually uses the value of SESSIONS to determine how many there are so YMMV). Alternatively, after the undo segments are idle for longer than 24 hours they'll drop to this number as well I believe. Now if your undo tablespace is set to AUTOEXTEND (an argument I won't get into now), it presumably extended because you had lots of undo being generated. Won't that happen again? If so, what's the point in swapping to rollback segments, using those to grow just as big, then manually shrinking either the rollback segments or the tablespace?

This seems an exercise in futility to me, because you're just going to end up with the same size rollback segments and tablespace in the future when all this undo is generated again. Whether they're undo segments or rollback segments makes no difference to how much they grow. Using undo segments simply eliminates some of the unnecessary management overhead we used to have using rollback segments.

Now, if the undo generation is a once-off affair, then maybe your shrinking requirement makes sense. But even there, I'd probably have two undo tablespaces, use one to do the big undo generation and then switch to the other one.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Wed Feb 05 2003 - 11:35:02 CST