How to Drop existing undo tablespace ?

You cannot drop an undo tablespace unless you have another undo tablespace. I am demonstrating the scenario to drop the existing undo table and create a new one.

Purpose of dropping an existing may differ. I my case, I wanted to move undo tablespace to different mountpoint.


select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_data_files;


FILE_NAME                                      TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------                --------------------            ---------------------
/u01/app/oracle/oradata/TEST/users01.dbf        USERS                        .008544922

/u01/app/oracle/oradata/TEST/undotbs01.dbf     UNDOTBS1                      15.7519531

/u01/app/oracle/oradata/TEST/sysaux01.dbf       SYSAUX


Thats my existing undo tablespace, Now I will create a new undo tablespace.


create undo tablespace undo2 datafile  '/u02/oradata/TEST/undotbs_01.dbf' size 12G;

Tablespace created.


Now, make the new undo tablespace as default tablespace.


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

System altered.

Now we can go ahead and drop the old undo tablespace i.e. UNDOTBS1

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.



Good Luck!!!!