Hi again,
Thank you for all the responses i had to my last question, very much appreciated.
Still on the subject of undo tablespace management, another question please. I am aware that I can create a new undo tablespace, switch it to be active and drop the old one. I can also resize an existing undo datafile to a size greater than the existing size. My question however is - Should I also be able to resize to a size less than the existing allocated size where all of the allocated size is not getting used? For example:
- undotbs1 has only 1 datafile 1 G size
- space in use ('active' or 'unexpired' ) extents does not go over 20M
I tried to resize the datafile to 500M but it comes back with :
Error starting at line : 3 in command -
ALTER DATABASE DATAFILE 'xyz.dbf'
RESIZE 500M
Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 - "file contains used data beyond requested RESIZE value"
*Cause: Some portion of the file in the region to be trimmed is
currently in use by a database object
*Action: Drop or move segments containing extents in this region prior to
resizing the file, or choose a resize value such that only free
space is in the trimmed.
There are no transactions running, I have tried bouncing the database, on coming back up, it still shows space being used as same value of approx 20M. Can someone please advise me, am I supposed to be able to shrink this undo datafile? I have purged the recycle bin.
thank you.