Drop And Recreate Undo Tablespace

DROP AND RECREATE UNDO TABLESPACE

In some cases UNDO tablespace aquire GBs of space and we feel to reclaim it.
By recreating UNDO tablespace lots of the space will be released.

To recreate the undo tablespace follow the below steps:

1. To Check the existing UNDO details:

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

2. Create a new undo tablespace:

SQL> create undo tablespace UNDO01 datafile 'E:\app\ORADATA\ORCL\UNDO01.dbf' size 1g;

Tablespace created.

3. Update undo_tablespace parameter

SQL> alter system set undo_tablespace=UNDO01 scope=both;

System altered.

SQL>  show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO01

4. Check for the active sessions for rollback segment in old tablespace

set pagesize 200
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 = 'UNDOTBS01'
);


NAME                           STATUS          USERNAME         SID    SERIAL#
------------------------------ --------------- --------- ---------- ----------
_SYSSMU10_46915872145$          PENDING OFFLINE DBACLASS         193      732119

5. Kill the active session(s) which is using old tablespace

SQL> alter system kill session '193,732119' immediate;

System altered.

6. Drop the old undo tablespace

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.