Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to shrink undo tablespace

Re: How to shrink undo tablespace

From: Daniel Fink <danielwfink_at_yahoo.com>
Date: 6 Dec 2005 07:42:41 -0800
Message-ID: <1133883761.660164.45390@g47g2000cwa.googlegroups.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US