It is not an easy task to drop the undo tablespace. Once I have to delete the undo tablespace due to some reason and I find that it is not straight forward to delete the undo tablespace. I got the following error while dropping the error :
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE D:\ORACLE\ORADATA\NOIDA\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 the following steps:
1.) Create new undo tablespace
2.) Make it default tablespace and undo management manual by editing 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 editing parameter file and restart the database
Step 1 : Create Tablespace : Create undo tablespace undotbs2
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\ORADATA\NOIDA\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
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE D:\ORACLE\ORADATA\NOIDA\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 the following steps:
1.) Create new undo tablespace
2.) Make it default tablespace and undo management manual by editing 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 editing parameter file and restart the database
Step 1 : Create Tablespace : Create undo tablespace undotbs2
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\ORADATA\NOIDA\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