How to shrink Oracle database “undo” tablespace data file?

Sometimes it happens that we accidentally or unconsciously run very big DML transactions in database. Especially in ones, which dedicated for development purposes. As change history must be saved somewhere to enable a rollback of transaction, memory is necessary for that. And this is purpose which undo tablespace is serving for in Oracle database. And after some huge transactions it can be autoextended to enormous size. Which basically in normal database run is not necessary. But it stays extended and occupy disk space. And now we want to shrink it and release some of disk space. This is how it is done:

1. Check which file is used for undo tablespace

   select tablespace_name,file_name from dba_data_files;

   

2. Create new undo tablespace. Recommended at the same filesystem path to keep consistency.

   create undo tablespace UNDOTBS2 datafile '/opt/oracle/database/oradata/promis/undotbs02.dbf' size 100M;

3. Tell the system to use new undo tablespace from current point of time. This is done by changing system parameter:

   alter system set undo_tablespace=UNDOTBS2;

4. Restart database instance. Using database server command line connect to DB instance as sysdba. Shutdown immediate and startup database instance:

   $sqlplus / as sysdba
   SQL>shutdown immediate
   ...
   SQL>startup
   

   If you experience issues by connecting to database instance directly from server command line, please check this post – Why can’t connect to Oracle instance using “sqlplus / as sysdba” ?

5. Check if all related old undo tablespace segments went OFFLINE:

   select tablespace_name, segment_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1'

   If some of them still have status ONLINE, take them offline by executing (example):

   alter rollback segment "_SYSSMU3_3285411314$" offline;

   If some of them have status NEEDS RECOVERY, you’ll need a bit more effort to drop them. How to proceed with it, please check post – How to drop corrupted “undo” tablespace segment of Oracle database?

6. When there is no other status then OFFLINE available between segments belonged to old undo tablespace, you can drop it:

   drop tablespace UNDOTBS1 including contents and datafiles;

Thats it. Enjoy extra free disk space!

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0