Re: How to shrink undo space?

Roger Redford wrote:

> 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

You can't.

Daniel Morgan Received on Fri Feb 07 2003 - 15:01:42 CST