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;

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

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Handle error with Step 5 ,6 and 7

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;