--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.
[54526538] **** active transactions found in undo tablespace 4 - moved to pending switch-out state.
[54526538] 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
[43385080] 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 where tablespace_name = 'undotbs3';
--因为undo_retention 的原因，无法立即删除原 undo 表空间，只能等待原 undo 表空间的回滚段全部变为 offline 后才能删除。
--可以在任何时间新建 undo 表空间，切换实例的当前 undo 表空间，但是只有等到原有 undo 表空间中的回滚段全部 offline 后才能删除。
--可以通过修改 undo_retention 让原 undo 表空间的状态切换变快
set linesize 200
select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name = 'undotbs1' and status = 'offline';
select count(*) from dba_rollback_segs where tablespace_name = 'undotbs1' and status = 'online';
--输出应该为 0
--5、当确定原 undo 表空间回滚段全部 offline 后，将该表空间置为 offline
alter tablespace undotbs1 offline;
sql> select tablespace_name,status,contents from dba_tablespaces where tablespace_name = 'undotbs2';
tablespace_name status contents
------------------------------ --------- ---------
undotbs2 online undo
--6、删除原有 undo 表空间极其数据文件
drop tablespace undotbs1 including contents and datafiles;
alter tablespace rename undotbs3 to undotbs1;
alter system set undo_tablespace=undotbs2;
sql> alter system set undo_retention=900;
system altered.
作者：xiangsir
9063573
qq：444367417
msn：xiangsir@hotmail.com