Oracle11gUndo表空間切換

--1、查看實(shí)例當(dāng)前所用 undo 表空間及 undo 相關(guān)參數(shù) SQL show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 86400 undo_tablesp

--1、查看實(shí)例當(dāng)前所用 undo 表空間及 undo 相關(guān)參數(shù)


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、切換實(shí)例當(dāng)前的 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 表明切換時(shí)當(dāng)前undo tablespace 中還存在正在進(jìn)行的事物（所以做切換的時(shí)候最好在無事務(wù)進(jìn)行）


--4、查看 undo 表空間切換是否生效
SQL> show parameter undo


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS3
SQL>
--新切換的 undo 表空間 UNDOTBS3 的混滾段應(yīng)該是 online 狀態(tài)
set linesize 200
select SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS from dba_rollback_segs where tablespace_name = 'UNDOTBS3';


--因?yàn)閡ndo_retention 的原因，無法立即刪除原 undo 表空間，只能等待原 undo 表空間的回滾段全部變?yōu)?offline 后才能刪除。
--可以在任何時(shí)間新建 undo 表空間，切換實(shí)例的當(dāng)前 undo 表空間，但是只有等到原有 undo 表空間中的回滾段全部 offline 后才能刪除。
--可以通過修改 undo_retention 讓原 undo 表空間的狀態(tài)切換變快


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';


--輸出應(yīng)該為 0


--5、當(dāng)確定原 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 表空間極其數(shù)據(jù)文件


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

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com