Re: how to shrink undo tablespace on 10GR2?

From: Niall Litchfield <>
Date: Wed, 20 Oct 2010 08:26:36 +0100
Message-ID: <>

It's one of the reasons why undo tablespaces should have datafiles set at a fixed size and some sort of sensible undo_retention configured. Still no-one seems to know how much undo they generate per time period anyway so perhaps that's just wishful thinking on my part.

On 20 Oct 2010 01:52, "Daniel W. Fink" <> wrote:

This is one of the 'nice features' of automatic undo. Since you are no longer able to manually drop undo segments (in fact, you can't drop undo segments without dropping the whole undo tablespace), all it takes is for a database to experience high undo activity and a segment to be automatically created with a block near the 'tail end' of the file. Since you can't drop the segment and deallocation is spotty, you get a file with a lot of free space, but none reclaimable because of the segment w/the high water mark block. Oracle really should allow for the dropping of undo segments in this case.

As others have said, your only choice is to create a new undo tablespace and drop the old one.

On 10/19/2010 08:43 AM, dba1 mcc wrote:
> We have 10GR2 on Linux server. I found one database "...

Received on Wed Oct 20 2010 - 02:26:36 CDT

Original text of this message