1. 檢視 undo 相關引數
show parameter undo
NAME TYPE VALUE
------------------ -------- ----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS

2. 檢視當前 undo 表空間的資料檔案
select file_name,bytes from dba_data_files where tablespace_name = 'UNDOTBS1';

3. 每個 undo 段最多包含的事務數 (10g 已經廢棄)
10g 預設一個 undo 段最多一個事務, undo 段不夠時, 自動建立 undo 段
當 undo 表空間不夠時, undo 段可以包含 1 個以上的事務 (undo段頭可能會出現等待)
show parameter rollback
NAME TYPE VALUE
------------------------------------ --------------- -------
transactions_per_rollback_segment integer 5

4. 檢視 undo 回滾段狀態
select * from dba_rollback_segs;
select a.usn, b.name, a.status, a.xacts, a.rssize/1024/1024/1024, a.hwmsize/1024/1024/1024, a.shrinks from v$rollstat a, v$rollname b where a.usn = b.usn order by a.rssize;

5. 建立新回滾表空間
create undo tablespace undo datafile '/usr/lib/oracle/xe/oradata/XE/undo.dbf' size 10m;

6. 切換 undo 表空間
alter system set undo_tablespace = undotbs scope = both;

7. 確認回滾段狀態
反覆確認 undo 回滾段狀態, 直到全部回滾段為 online 狀態, 沒有pending_offline 狀態, 最好到達 undo_retention 所限定的時間之後再刪除
select a.usn, b.name, a.status, a.xacts, a.rssize/1024/1024/1024, a.hwmsize/1024/1024/1024, a.shrinks from v$rollstat a, v$rollname b where a.usn = b.usn order by a.rssize;

8. 刪除原回滾表空間
drop tablespace undotbs including contents and datafiles;

EOF

UNDO表空間空間回收及切換

相關文章