You can try to shrink the datafile of the UNDO tablespace. Query DBA_DATA_FILES to determine the name of the datafiles of the UNDO tablespace you are using. When you find the file name, you can issue a command similar to the following:
ALTER DATABASE DATAFILE '/directory/undo_ts01.dbf' RESIZE xxxM;Supply the file name in the above command. The new size should be smaller than your older datafile size.
It would not be surprising to find that an error is raised when this operation occurs. You can only release space at the end of the datafile if it is free. It may be likely that this space has been allocated by an undo segment. If that is the case, then your alternative is to create a new UNDO tablespace and point the database to use it. The following steps should do the trick!
- Create a new UNDO tablespace.
CREATE UNDO TABLESPACE undo_ts2 DATAFILE '/directory/undo_ts2_01.dbf' SIZE xxxM;
- Modify the database parameter to use the new UNDO tablespace.
ALTER SYSTEM SET undo_tablespace=undo_ts2 SCOPE=BOTH;
This was first published in June 2005