Why and How to Drop Undo Tablespace ?

A condition may be occur when we have to  drop the Undo tablespace. Undo tablespace may be drop in various scenarios .In my case ,Once i have imported few tables with table_exists_action=append parameter in  database and these tables has created lots of undo's  i.e;  near about 102GB. So when we backup the database ,the backup size increases, i.e; backup consumes lots of space. Another scenario may be possible that while clonning if the undo tablespace get missed, then we can recover by just dropping the undo tablespace. Below is demo for drop and re-creating the undo tablespace. 

Step 1 : Shutdown immediate

SQL> shut immediate

Step 2 : Create pfile from spfile and edit pfile to set undo_management=manual (if it is set auto then set it to manual and if this parameter is not in pfile than set it i.e, undo_management=manual  otherwise it will consider it "auto"  management

Step 3 : Startup pfile=<modified pfile>

Step 4 : Drop undo tablespace as

SQL> drop  tablespace <undo_name> including contents and datafiles.

Step 5 : Create Undo tablespace

SQL> create undo tablespace undotbs1 datafile <location> size=100M;

Step 6 : Shutdown the database and edit pfile to reset  "undo_management=AUTO"

Step 7 : create spfile from pfile

SQL> create spfile from pfile=<pfile_location>

Step 8 : Startup the database

SQL> startup 


Enjoy   :-)