This post provides the steps to drop an Undo tablespace.
The Undo tablespace can be dropped if it is not the default Undo tablespace of the database and all the Undo segments in it are OFFLINE.
1. check whether the Undo tablespace to be dropped is the default Undo tablespace:
sql> show parameter undo
The tablespace name mentioned by the parameter UNDO_TABLESPACE is the current default Undo tablespace.
2. If the UNDO_TABLESPACE is set to the one to be dropped, create a new Undo tablespace and switch to the new one. Else, skip to step 3. Else, determine the size of the datafile(s) for your current undo tablespace “UNDOTBS1”:
sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name=[tablespace_name from the above output];
For example:
sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1;
Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
sql> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 500M;
Switch to the new Undo tablespace.
sql> alter system set undo_tablespace=UNDOTBS2 scope=both;
3. Check the status of the Undo segments in the Undo tablespace to be dropped.
sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
If there are Undo segments with status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.
sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=[undo tablespace to be dropped];
For example:
sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1';
The status ‘PARTLY AVAILABLE‘ means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. In case of a dead transaction, you will have to wait until the recovery is done.
If the Undo Segment status is ‘NEEDS RECOVERY‘, please refer the post below to recover the Undo segment.
4. If all the Undo segments in the tablespace to the dropped is of status OFFLINE, then drop the tablespace.
sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
5. Verify and then drop:
sql>Drop tablespace [tablespace_name] including contents and datafiles;
For example:
sql> Drop tablespace UNDOTBS1 including contents and datafiles;