Oracle 9i Database UNDO tablespace release

When checking the disk space used by the database files, we found that the undo tablespace used as much as 4 GB this weekend. Up to 24 GB!

According to the normal volume of things, it will not grow so fast. Now there are a few GB of memory left on the disk, so I decided to switch the undo tablespace to a relatively new data file.

First, use the sys user to create an undo tablespace undotbs02

The initial size is 2 GB. The auto-scaling attribute is disabled, and the disk space is more than 3 GB :(

SQL> create undo tablespace undotbs02 datafile '/usr/Oracle/oradata/db/undotbs01.dbf' size 2048 m autoextend off;

Then activate the new tablespace.

SQL> alter system set undo_tablespace = undotbs01;

The system has been changed!

Check for rollback

Select count (*) from v $ transaction

No result indicates that no transaction is rolled back.

Check whether all the rollback segments of the previous undo tablespace are offline.

Select * from dba_rollback_segs: the query results still have ONLINE rollback segments. In this case, you cannot delete data files.

The original undo tablespace can be deleted only after all the old rollback segments are OFFLINE.

So SQL> drop tablespace undotbs1 including contents and datafiles;

The tablespace has been deleted!