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;