ORACLE線上切換undo表空間

檢視原undo相關引數

SHOW PARAMETER UNDO;

   查詢資料庫undo使用情況

SELECT seg.tablespace_name "Tablespace Name",

       ts.bytes / 1024 / 1024 "TS Size(MB)",

       ue.status "UNDO Status",

       count(*) "Used Extents",

       round(sum(ue.bytes) / 1024 / 1024, 2) "Used Size(MB)",

       round(sum(ue.bytes) / ts.bytes * 100, 2) "Used Rate(%)"

  FROM dba_segments seg,

       DBA_UNDO_EXTENTS ue,

       (SELECT tablespace_name, sum(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) ts

 WHERE ue.segment_NAME = seg.segment_NAME

   and seg.tablespace_name = ts.tablespace_name

 GROUP BY seg.tablespace_name, ts.bytes, ue.status

 ORDER BY seg.tablespace_name;

檢視  undo  表空間的大小

SELECT D.TABLESPACE_NAME,D.FILE_NAME, SUM(D.BYTES) / 1024 / 1024 MB

  FROM DBA_DATA_FILES D

 WHERE D.TABLESPACE_NAME LIKE 'UNDO%'

 GROUP BY D.TABLESPACE_NAME,D.FILE_NAME;

建立新undo空間

create undo tablespace undo_x datafile 'f:\ORACLE\ORADATA\XIFENFEI\undo_1-1.dbf' size 10M autoextend on next 10M maxsize 30G;

查詢歷史undo是否還有事務(包含回滾事務)

SELECT a.tablespace_name,
      a.segment_name,
      b.ktuxesta,
      b.ktuxecfl,
      b.ktuxeusn || '.' || b.ktuxeslt || '.' || b.ktuxesqn trans
 FROM dba_rollback_segs a, x$ktuxe b
WHERE a.segment_id = b.ktuxeusn
  AND a.tablespace_name = UPPER('&tsname')
  AND b.ktuxesta <> 'INACTIVE';

SELECT a.usn,

       a.name,

       b.status,

       c.tablespace_name,

       d.addr,

       e.sid,

       e.serial#,

       e.username,

       e.program,

       e.machine,

       e.osuser

  FROM v$rollname        a,

       v$rollstat        b,

       dba_rollback_segs c,

       v$transaction     d,

       v$session         e

 WHERE a.usn = b.usn

   AND a.name = c.segment_name

   AND a.usn = d.xidusn

   AND d.addr = e.taddr

   AND b.status = 'PENDING OFFLINE';

--因為有undo_retention引數,所以不能簡單的透過確定該sql無事務就可以刪除原undo   切換undo表空間(無論是否有事務,均可以切換[最好是無事務時切換],但是不能直接刪除原undo表空間)

alter system set undo_tablespace=UNDOTBS2 scope=both;

ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=BOTH SID='boss1';

alert日誌現象,表明原undo還有事務

Sun Jun 17 20:10:45 2012
Successfully 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 SYSTEM SET 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,status from dba_rollback_segs;

離線原undo表空間

alter tablespace undotbs1 offline;

確定原undo回滾段全部offline,直接刪除

drop tablespace undotbs1 including contents and datafiles;

切換undo表空間一句話：  新建undo幾乎是任何時候都可以執行切換undo表空間命令,如果要刪除歷史undo需要等到該undo空間所有回滾段全部offline.千萬別在尚有回滾段處於online狀態,強制刪除資料檔案.