How to switch Oracle database undo tablespace to new undo tablespace

Check default undo tablespace

SQL> show parameter undo

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled		     boolean	 FALSE
undo_management 		     string	 AUTO
undo_retention			     integer	 900
undo_tablespace 		     string	 UNDOTBS1

create new undo tablespace is undotbs2

SQL> create undo tablespace undotbs2 datafile size 1G autoextend on maxsize unlimited ;

Tablespace created.

switch undo_tablespace to new undo tablespace

SQL> alter system set undo_tablespace='UNDOTBS2' scope=both ;

System altered.

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;

TABLESPACE_NAME 	       STATUS		  COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1		       OFFLINE			11
SYSTEM			       ONLINE			 1
UNDOTBS2		       ONLINE			16

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.

when old undo tablespace is offline, then drop old undo tablespace.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.