參考MOS文章How to Shrink the datafil

操作步驟如下

    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.

可能碰到的問題

如果要drop的undo tablespace還有active事務的undo資訊，那麼需要等到事務結束之後，才能成功執行drop操作，否則會丟擲ORA-30013: undo tablespace ‘%s’ is currently in use錯誤。一般的做法是，重啟一次資料庫，這樣就可以確保所有事物使用的都是新建的undo tablespace。

通過以下sql可以檢視當前系統中undo segment的情況：

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