参考MOS文章How to Shrink the datafile of Undo Tablespace (文档 ID 268870.1)
操作步骤如下
Create a new undo tablespace with a smaller size:
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' 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