How To resize undo tablespace in Oracle

Time:2019-10-9

Refer to the MOS article How to Shrink the data file of Undo Table space (document ID 268870.1)

The operation steps are as follows

  • Create a new undo tablespace with a smaller size:
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size <new size>;
  • Set the new tablespace as the undo tablespace to be used:
SQL> alter system set undo\_tablespace=undo_rbs1;
  • Drop the old undo tablespace:
SQL> drop tablespace undo_rbs0 including contents.

Probable problems

If the undo tablespace to drop and the undo information of the active transaction are available, the drop operation can not be successfully run until the transaction is completed, otherwise ORA-30013: undo tablespace’%s’is currently in use error will be thrown. The general practice is to restart the database once, so that you can ensure that everything is using the new undo tablespace.

The following SQL allows you to view undo segments in the current system:

SQL> select owner,segment_name,tablespace_name
     from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME
------ ------------------------------ ------------------------------
PUBLIC _SYSSMU1$                      RBS
PUBLIC _SYSSMU2$                      RBS
PUBLIC _SYSSMU3$                      RBS
PUBLIC _SYSSMU5$                      RBS
PUBLIC _SYSSMU7$                      RBS
PUBLIC _SYSSMU9$                      RBS
PUBLIC _SYSSMU10$                     RBS
PUBLIC _SYSSMU8$                      RBS
PUBLIC _SYSSMU6$                      RBS
PUBLIC _SYSSMU4$                      RBS
SYS    SYSTEM                         SYSTEM
PUBLIC _SYSSMU11$                     UNDO_RBS1
PUBLIC _SYSSMU12$                     UNDO_RBS1
PUBLIC _SYSSMU13$                     UNDO_RBS1
PUBLIC _SYSSMU14$                     UNDO_RBS1
PUBLIC _SYSSMU15$                     UNDO_RBS1
PUBLIC _SYSSMU16$                     UNDO_RBS1
PUBLIC _SYSSMU17$                     UNDO_RBS1
PUBLIC _SYSSMU18$                     UNDO_RBS1
PUBLIC _SYSSMU19$                     UNDO_RBS1
PUBLIC _SYSSMU20$                     UNDO_RBS1