Steps to change UNDO tablespace in Oracle Database

Step 1: Connect with database

sqlplus / as sysdba
SQL> show parameter undo
NAME TYPE VALUE
--------------- ------- ----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Step 2:  Create a new undo tablespace.

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/radata/oraxpo/undotbs201.dbf'
SIZE 50M AUTOEXTEND ON NEXT 5M;

Step 3: Switch to the new undo tablespace created by alter command:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

Step 4: Drop the old undo tablespace

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Step 5: Check the object which is using old undo tablespace:

set lines 10000
column name format a10
SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

Step 6: Check the session which is running currently:

column username format a6
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 = 'UNDOTBS1'
);

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         147          4

Step 7: Kill and check the session.

SQL> alter system kill session '147,4';
System altered.
-- verify again
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 = 'UNDOTBS1'
);
no rows selected

Step 8: The UNDO_RETENTION time passed means wait for 15 min and try to drop the tablespace.
In my case it is 900 seconds i.e. 15 minutes.

Step 9: Drop the tablespace undo with following commands

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;