Change or switch undo tablespace in Oracle database

This is a mostly asked question on different oracle forums; How do I change the UNDO tablespace for my database and drop the old one? and the answer often is; Its very simple daa. But I don't think its that simple. The complexity comes in if there were pending transactions in your old undo tablespace and you have already switched to the new one and when you try to drop the old one it says "Tablespace is in use".



How to switch the database to a new UNDO tablespace and drop the old one


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2


The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS2A.


SQL> CREATE UNDO TABLESPACE UNDOTBS2A DATAFILE '+MM_ARCH01' SIZE 10G;

Tablespace created.

Switch the database to a new UNDO tablespace 

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2A SID='XXXXX' SCOPE=BOTH;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2A


Drop the old UNDO one


SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

With the alter system set undo_tablespace=UNDOTBS2A, the database UNDO tablespace is changed and any new transaction's undo data will go to the new tablespace i.e. UNDOTBS2A. But the undo data for already pending transaction (e.g. the one initiated by any other user before the database UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old 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

----------              ---------------

_SYSSMU13$ PENDING OFFLINE

_SYSSMU19$ PENDING OFFLINE

The above query shows the name of the UNDO segment in the UNDOTBS2 tablespace and its status. Now lets see which users/sessions are running this pending transaction.

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

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU19$ PENDING OFFLINE SYS           821       9889
_SYSSMU13$ PENDING OFFLINE SYS           837       9431

So this is SSYS with SID=821,837  and SERIAL#=49889,9431. Since we know now the user, we can go to him/her and request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However, if this is not possible (say the user initiated the transaction and left for annual leave :) and trust me this happens) you may go ahead and kill the session to release the undo segments in the UNDOTBS2 tablespace.

SQL> alter system kill session '821,9889';

System altered.

SQL> alter system kill session '837,9431';

System altered.

SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#

  2  FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

  3  WHERE  a.usn = b.usn

  4  AND    a.usn = c.xidusn

  5  AND    c.ses_addr = d.saddr

  6  AND    a.name IN (

  7               SELECT segment_name

  8               FROM dba_segments

  9               WHERE tablespace_name = 'UNDOTBS1'

 10              );

no rows selected

As we can see once the session is kills we don't see anymore segments occupied in the UNDOTBS2 tablespace. Lets drop UNDOTBS2.

SQL>  DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

 DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

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

If you are retaining undo data then you still won't be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

SQL>  DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Disclaimer: If you feel this content related to your content  please feel to contact me to remove this information.  I am sharing the knowledge with people whoever in the need which is learning daily basis.