ORACLE在线切换undo表空间

切换undo的一些步骤和基本原则

原文：http://www.xifenfei.com/3367.html

 

查看原undo相关参数

?

1	SHOW PARAMETER UNDO;

 

创建新undo空间

?

1	create undo tablespace undo_x datafile 'E:\ORACLE\ORADATA\XIFENFEI\undo_xifenfei.dbf' size 10M autoextend on next 10M maxsize 30G;

 

查询历史undo是否还有事务(包含回滚事务)

?

1 2 3 4 5 6 7 8 9

SELECT a.tablespace_name,        a.segment_name,        b.ktuxesta,        b.ktuxecfl,        b.ktuxeusn || '.' || b.ktuxeslt || '.' || b.ktuxesqn trans   FROM dba_rollback_segs a, x$ktuxe b  WHERE a.segment_id = b.ktuxeusn    AND a.tablespace_name = UPPER('&tsname')    AND b.ktuxesta <> 'INACTIVE';

 

--因为有undo_retention参数,所以不能简单的通过确定该sql无事务就可以删除原undo

 

切换undo表空间(无论是否有事务,均可以切换[最好是无事务时切换],但是不能直接删除原undo表空间)

?

1	alter system set undo_tablespace='undo_x';

 

alert日志现象,表明原undo还有事务

?

1 2 3 4 5 6 7 8 9 10

Sun Jun 17 20:10:45 2012 Successfully onlined Undo Tablespace 7. [36428] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state. [36428] active transactions found/affinity dissolution incompletein undo tablespace 2 during switch-out. ALTER SYSTEM SET undo_tablespace='undo_xifenfei' SCOPE=BOTH;    Sun Jun 17 20:11:38 2012 [36312] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state. Sun Jun 17 20:16:15 2012 [36312] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.

　　

--只能表明有事务,就算长时间未出现类似记录,不能证明一定可以删除原undo,因为undo_retention

 

查询回滚段情况(原undo表空间的回滚段全部offline,可以删除相关表空间)

?

1	select tablespace_name,segment_name,status from dba_rollback_segs;

　　

离线原undo表空间

?

1	alter tablespace undotbs1 offline;

　

确定原undo回滚段全部offline,直接删除

?

1	drop tablespace undotbs1 including contents and datafiles;

　　

切换undo表空间一句话：新建undo几乎是任何时候都可以执行切换undo表空间命令,如果要删除历史undo需要等到该undo空间所有回滚段全部offline.千万别在尚有回滚段处于online状态,强制删除数据文件.