How to dorp/recreate your Undo Tablespaces

> It is better to put the database in the mount mode while doing these activities.
> If the database is up and running you may face "ORA-30013: undo tablespace '%s' is currently in use." error while dropping the old Undo tablespace, this error indicates that you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transactions to be committed or rolled back. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

1- Collect the required data about your Undo Tablespaces:

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='UNDO';

TABLESPACE_NAME
------------------------------
APPS_UNDOTS1

 SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS1
SQL>

SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

FILE_NAME                 FILE_ID TABLESPACE_NAME            BYTES      BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES    MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/PROD/db/apps_st/data/undo01.dbf    379 APPS_UNDOTS1           4194304000      512000 AVAILABLE        379 YES 3.4360E+10      4194302     12800 4194238464      511992 ONLINE
/oracle/PROD/db/apps_st/data/undo02.dbf     19 APPS_UNDOTS1           4404019200      537600 AVAILABLE         19 YES 3.4360E+10      4194302     12800 4402970624      537472 ONLINE

SQL>

- Total size of the Undo Tablespace:

SQL> SELECT sum(bytes)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

SUM(BYTES)/1024/1024/1024
-------------------------
        8.0078125

SQL> !ls -ltrh /oracle/PROD/db/apps_st/data/undo0*
-rw-r----- 1 orstage dba 4.2G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo02.dbf
-rw-r----- 1 orstage dba 4.0G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo01.dbf

SQL>

2- Create a new undo tablespace:

SQL> create undo tablespace APPS_UNDOTS2 datafile '/oracle/PROD/db/apps_st/data/undoNew.dbf' size 1000M;

3- Set the new tablespace as the undo tablespace to be used:

SQL> alter system set undo_tablespace=APPS_UNDOTS2 scope=spfile;

SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS2
SQL>   

4- Drop the old undo tablespace:

    SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;

5- Recreate the old undo tablespace with a smaller size:

    SQL> create undo tablespace APPS_UNDOTS1 datafile '/oracle/PROD/db/apps_st/data/undo01.dbf' size 10240M '/oracle/PROD/db/apps_st/data/undo02.dbf' size 10240M;

6- Set the old tablespace as the undo tablespace to be used:

    SQL> alter system set undo_tablespace=APPS_UNDOTS1 scope=spfile;

 7- Drop the temp undo tablespace:

    SQL> drop tablespace APPS_UNDOTS2 including contents and datafiles;

Regards,
Mohamed