Oracle 11g Undo 表空间切换.pdf

DDBBAA nneevveerr sslleeeepp--DDBBAA，，永永不不眠眠 FFooccuuss oonn OOrraaccllee DDaattaabbaassee,, GGooddeennGGaattee aanndd UUnniixx [[ 顶顶]] OOrraaccllee 1111gg UUnnddoo 表表空空间间切切换换 分类： Oracle Database Architecture 2013-06-11 22:31 290人阅读 评论 (0) 收藏 举报 undo 表空间切换undo_retentionundo_tablespaceundo_management --1、查看实例当前所用 undo 表空间及 undo 相关参数 SQL show parameter undo NAME                                 TYPE        VALUE undo_management                      string      AUTO undo_retention                       integer     86400 undo_tablespace                      string      UNDOTBS2 --2、新建 undo 表空间 create undo tablespace UNDOTBS3 datafile  +DATA/hxcx/datafile/undotbs3_01.dbf size 30G autoextend on next 100m maxsize unlimited, +DATA/hxcx/datafile/undotbs3_02.dbf size 30G autoextend on next 100m maxsize unlimited; create undo tablespace UNDOTBS3 datafile  +DATA/hxcx/datafile/undotbs1_03.dbf size 30G autoextend on next 100m maxsize unlimited, +DATA/hxcx/datafile/undotbs1_04.dbf size 30G autoextend on next 100m maxsize unlimited; --3、切换实例当前的 undo 表空间 SQL alter system set undo_tablespace=UNDOTBS3; System altered. **** active transactions found in undo Tablespace 4 - moved to Pending Switch-Out state. active transactions found/affinity dissolution incompletein undo tablespace 4 during switch-out. ALTER SYSTEM SET undo_tablespace=UNDOTBS3 SCOPE=BOTH ; Mon Jun 03 09:49:53 2013 Undo Tablespace 4 successfully switched out. -- alert.log 表明切换时当前undo tablespace 中还存在正在进行的事物 （所以做切换的时候最好在无事务进行） --4、查看 undo 表空间切换是否生效 SQL show parameter undo NAME                                 TYPE        VALUE undo_management                      string      AUTO undo_retention                       integer     86400 undo_tablespace                      string      UNDOTBS3 SQL  --新切换的 undo 表空间 UNDOTBS3 的混滚段应该是 online 状态 set linesize 200 select SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS from dba_rollback_segs wher