ORACLE在線切換undo表空間

2024-08-29 13:54:58

字體：大中小

供稿：網(wǎng)友

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

切換undo的一些步驟和基本原則

查看原undo相關(guān)參數(shù)SHOW PARAMETER UNDO; 創(chuàng)建新undo空間create undo tablespace undo_x datafile 'E:/Oracle/ORADATA/XIFENFEI/undo_xifenfei.dbf' size 10Mautoextendon next 10M maxsize 30G; 查詢歷史undo是否還有事務(wù)(包含回滾事務(wù))SELECT a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl,b.ktuxeusn||'.'||b.ktuxeslt||'.'||b.ktuxesqn transFROM dba_rollback_segs a, x$ktuxe b WHERE a.segment_id = b.ktuxeusn AND a.tablespace_name = UPPER('&tsname')AND b.ktuxesta <> 'INACTIVE';

--因?yàn)橛衭ndo_retention參數(shù),所以不能簡(jiǎn)單的通過(guò)確定該sql無(wú)事務(wù)就可以刪除原undo

切換undo表空間(無(wú)論是否有事務(wù),均可以切換[最好是無(wú)事務(wù)時(shí)切換],但是不能直接刪除原undo表空間)

alter systemset undo_tablespace='undo_x'; alert日志現(xiàn)象,表明原undo還有事務(wù)Sun Jun 17 20:10:45 2012Successfully 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 SYSTEMSET 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.

--只能表明有事務(wù),就算長(zhǎng)時(shí)間未出現(xiàn)類似記錄,不能證明一定可以刪除原undo,因?yàn)閡ndo_retention

查詢回滾段情況(原undo表空間的回滾段全部offline,可以刪除相關(guān)表空間)select tablespace_name,segment_name,statusfrom dba_rollback_segs; 離線原undo表空間alter tablespace undotbs1 offline; 確定原undo回滾段全部offline,直接刪除drop tablespace undotbs1 including contents and datafiles;

切換undo表空間一句話：新建undo幾乎是任何時(shí)候都可以執(zhí)行切換undo表空間命令,如果要?jiǎng)h除歷史undo需要等到該undo空間所有回滾段全部offline.千萬(wàn)別在尚有回滾段處于online狀態(tài),強(qiáng)制刪除數(shù)據(jù)文件.