Re: How to shrink undo tablespace

cnwy_at_263.net wrote:
> Hello,
>
> Currently, my undotablespace is autoextented to 16G in Oracle 9i. It is
> too large to my application, so I want to shrink it to a appropriate
> size 4G, I am not sure whether the way switching current undo
> tablespace(16g) to a new one(4G) is the best?
>
> Any suggestions will be very appreciated !
>
> wy

You might be able to shrink the datafiles for the tablespace. All the normal rules about shrinking datafiles apply, with the added restriction that you cannot 'drop' undo segments if they have allocated space 'above' the size you want to reduce the datafile.

You can create a new undo tablespace and switch to it. It is best to do this at a time of very low activity as this can lead to ORA-01555 errors for currently running queries.

Of course, you might find yourself in the same situation if you don't address why the tablespace is now 16g in size. If 4g is appropriate, what process(es) caused the allocation of additional 12g? Until you solve that problem, you'll find yourself back in the same situation.

Regards,
Daniel Fink Received on Tue Dec 06 2005 - 09:42:41 CST