This post explains the steps to switch to a new Undo tablespace.
1. Check your current default Undo tablespace:
SQL> show parameter undo_tablespace
2. Determine the size of the datafile(s) for your current undo tablespace:
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;
3.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\11.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 5000M;
4. Switch to the new Undo tablespace
sql> alter system set undo_tablespace = undotbs2 scope=both;
5. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.
sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have 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';
6. If all the Undo segments in the old Undo 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;
7. Verify and then drop:
sql> Drop tablespace [tablespace_name] including contents and datafiles;
For example:
sql> Drop tablespace UNDOTBS1 including contents and datafiles;