Steps to Change UNDO Tablespace in Oracle Database


Step 1: Connect to the Database


sqlplus / as sysdba

Check the current UNDO tablespace:


SHOW PARAMETER UNDO;

Output:


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


ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

Step 4: Drop the Old UNDO Tablespace


DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

⚠ Error:


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

Solution: Follow Steps 5–7 to fix this.

Step 5: Identify Objects Using the Old UNDO Tablespace


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'
);

Output:


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

Step 6: Find Active Sessions Using Old UNDO Tablespace


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'
);

Output:


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

Step 7: Kill the Active Session


ALTER SYSTEM KILL SESSION '147,4';

Verify if the session is removed:


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'
);

If no rows are returned, the session is cleared.

Step 8: Wait for UNDO Retention Time

  • The system retains undo data for UNDO_RETENTION time.

  • Default 900 seconds (15 minutes).

  • Wait for 15 minutes before proceeding.

Step 9: Drop the Old UNDO Tablespace Again


DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;




Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment