oracle加undo+resize,How To resize undo tablespace in Oracle

参考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