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 "undo" tablespace has been extend to 32 GB size. I used 'dba_free_space" to check and found this data file actually is empty. I tried to "resize" it back to small size but I can only resize it to 28GB. I knew this may be cause by "high water mark". Does there has way to shrink it to small? Thanks. -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l