Re: shrinking undo tablespace

"sg" <s4v4g3_at_europe.com> wrote in message news:3D126E20.80707_at_europe.com...
> I am aware of that command however I mean undo tablespace not temporary
> tablespace,

Huge apologies. I should learn to read more carefully.

>for example if somehow my undo increases to a very big size
> how can I reduce it? In RBS we could shrink manually then reduce
> tablespace size but in 9i this seems very hard, or we have to wait SMON
> wakes up and frees up extents or we have to drop the undo tablespace and
> create a new one. Are there any more options?
>

Forget it. You are using automatic undo, so if the tablespace has grown, it's because Oracle thinks you need it that big (undo_retention etc etc). My only advice to students these days for tuning undo is: throw disk space at it. It's cheap, and you shouldn't care.

The alternative is to have a quite small secondary undo tablespace. Change the database's undo tablespace to the new one. That will cause all the undo in the old, proper tablespace to become dead (eventually -there may still be live transactions using the old tablespace), at which point you should be able to shrink it, using the same resize datafile command. Once it's shrunk, switch back to using the proper tablespace.

Be aware that doing this violates the entire point of automatic undo, including undo_retention. You render yourself liable to 1555s, and forget flashback.

Anyway, the point is that a database *can* have more than one undo tablespace at a time, and by switching between them, you render the one switched away from liable to the sort of maintenance you are thinking of.

And sorry for the stuff up about UNDO and TEMP again.

Regards
HJR
>
> Howard J. Rogers wrote:
>
> > Assuming you are therefore using the proper 'tempfile' type of temporary
> > tablespace, the command is:
> >
> > alter database tempfile 'D:\ORACLE\ORA92\DB9\TEMP01.DBF' resize 80m;
> >
> > (Pick a size to suite. Normal rules apply: if getting to the new size
would
> > result in real data being chopped off, it won't work).
> >
> > Regards
> > HJR
> >
> > "sg" <s4v4g3_at_europe.com> wrote in message
> > news:3D1256D3.1050707_at_europe.com...
> >
> >>sorry forgot the version number..... it's 9.2
> >>
> >>Howard J. Rogers wrote:
> >>
> >>
> >>>Version?
> >>>
> >>>HJR
> >>>
> >>>"sg" <s4v4g3_at_europe.com> wrote in message
> >>>
> > news:3D125536.40305_at_europe.com...
> >
> >>>>Hi
> >>>>
> >>>>Does anyone know how to shrink an undo tablespace? So far the only way
I
> >>>>can find is by create a new one and drop the old one!
> >>>>
> >>>>Any other way?
> >>>>
> >>>>
> >>>>
> >>>
> >
> >
>
Received on Thu Jun 20 2002 - 19:21:34 CDT