Re: How to shrink undo space?

Hi all,

Thanks for responding. But I think that perhaps something was missed.

One undo segment grew to 20 GIGS because of a bad PROCESS that didn't commit right. Disk space was not the underlying problem.

So now there was one undo segment dominating the entire undo 20 gig tablespace. We couldn't even open new sessions to the database! Freeing up UNDO was something that was required immediately.

We actually did drop and recreate the tablespace, taking at least a half hour.

But I used to be able shrink RBS in seconds. And it was perfectly reliable. So, the question was, how to shrink it, as I used to be able to? No matter how fast you type, a single command is going to be executed faseter than many.

Has anyone done this? What has your experience been? (Unfortunately, I haven't had time to experiment the past few days.)

Thanks

dba_222_at_yahoo.com (Roger Redford) wrote in message news:<a8c29269.0302050827.c1ff936_at_posting.google.com>...
> 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
Received on Fri Feb 07 2003 - 12:24:19 CST