How to drop UNDO Tablespace

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".

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS                               D:\ORACLE\ORADATA\DATAFILE\USERS01.DBF
UNDOTBS1                       D:\ORACLE\ORADATA\DATAFILE\UNDOTBS01.DBF
SYSAUX                             D:\ORACLE\ORADATA\DATAFILE\SYSAUX01.DBF
SYSTEM                            D:\ORACLE\ORADATA\DATAFILE\SYSTEM01.DBF
EXAMPLE                          D:\ORACLE\ORADATA\DATAFILE\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

As the error indicate that the undo tablespace is in use so i issue the following command.

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1  offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace.

Therefore, to drop undo  tablespace, we have to perform following steps:

 1) Create new undo tablespace
 2) Make it defalut tablepsace and undo management manual by editting parameter file and restart it.
 3) Check the all segment of old undo tablespace to be offline.
 4) Drop the old tablespace.
 5) Change undo management to auto by editting parameter file and restart the database

Step 1 : Create Tablespace   :  Create undo tablespace undotbs2    

SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\DATAFILE\UNDOTBS02.DBF'  size 100M;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             360711792 bytes
Database Buffers           58720256 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

Step 3: Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------ ------------------------------ ------------------------------ ----------------
SYS                 SYSTEM                                     SYSTEM                            ONLINE
PUBLIC       _SYSSMU10_1192467665$          UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU1_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU2_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU3_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU4_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU5_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU6_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU7_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU8_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU9_1192467665$           UNDOTBS1                       ONLINE
PUBLIC      _SYSSMU12_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU13_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU14_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU15_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU11_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU17_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU18_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU19_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU20_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU16_1304934663$          UNDOTBS2                        OFFLINE

21 rows selected.

If anyone the above segment is online then change it status to offline by using below command.

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

Step  5 : Change undo management to auto and restart the database

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             364906096 bytes
Database Buffers           54525952 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace

NAME                                       TYPE        VALUE
------------------------------------   ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!