Oracle 11g Undo 表空间切换

–1、查看实例当前所用 undo 表空间及 undo 相关参数 SQL show parameter undo NAME TYPE VALUE ———————————— ———– —————————— undo_management string AUTO undo_retention integer 86400 undo_tablesp

–1、查看实例当前所用 undo 表空间及 undo 相关参数

SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management 本文来源gaodai#ma#com搞*!代#%^码网5 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：[email protected]

喜欢 (0)赏

[搞代码]

分享 (0)