ORACLE在線切換undo表空間

2024-08-29 13:54:58

字體：大中小

來源：轉載

供稿：網友

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

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

查看原undo相關參數SHOW PARAMETER UNDO; 創建新undo空間create undo tablespace undo_x datafile 'E:/Oracle/ORADATA/XIFENFEI/undo_xifenfei.dbf' size 10Mautoextendon next 10M maxsize 30G; 查詢歷史undo是否還有事務(包含回滾事務)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';--因為有undo_retention參數,所以不能簡單的通過確定該sql無事務就可以刪除原undo

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

alter systemset undo_tablespace='undo_x'; alert日志現象,表明原undo還有事務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.--只能表明有事務,就算長時間未出現類似記錄,不能證明一定可以刪除原undo,因為undo_retention 查詢回滾段情況(原undo表空間的回滾段全部offline,可以刪除相關表空間)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幾乎是任何時候都可以執行切換undo表空間命令,如果要刪除歷史undo需要等到該undo空間所有回滾段全部offline.千萬別在尚有回滾段處于online狀態,強制刪除數據文件.