Release/Shrink UNDO tablespace

  

Sometimes there will be situations where we run out of DB storage due to huge undo growth and we wanted to shrink it to release some space for other datafiles in the database and The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace

Create New UNDOTBS2 tablespace

SQL> create undo tablespace UNDOTBS2 datafile '+DATAC1' size 500M;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

System altered.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS1                       OFFLINE                  12

UNDOTBS2                       ONLINE                   10

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.

Check any segments other than OFFLINE status using below

SQL>  select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

no rows selected

Since there are no segments then we can easily drop the tablespace and release space

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS2                       ONLINE                   10

UNDOTBS3                       OFFLINE                  22

Now reset to same tablespace by creating same tablespace again

SQL> create undo tablespace UNDOTBS1 datafile '+DATAC1' size 500M;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE =UNDOTBS1 SCOPE=BOTH;

System altered.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS1                       ONLINE                   15

UNDOTBS2                       OFFLINE                  10

Sometimes dropping the old tablespace may give ORA-30013: undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.   

Also, According to ORACLE DOC:

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.