Drop Unused UNDO Tablespace

1) Check how many UNDO tablespaces are available on your database:

SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
UNDOTBS2                       UNDO


2) Check which UNDO tablespace is being used currently:

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1


3) Validate that, any object that it is using other undo_tablespace than current undo_tablespace.

set pagesize 100
set lines 200
set long 999
col username for a9
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
 WHERE tablespace_name = 'UNDOTBS2');

4) If none of the object is using second undo_tablespace, then use below command to drop the unused undo_tablespace.

SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;