關於Oracle 釋放過度使用的Undo資料表空間

產生問題的原因主要以下兩點：
1. 有較大的事務量讓Oracle Undo自動擴充，產生過度佔用磁碟空間的情況；
2. 有較大事務沒有收縮或者沒有提交所導制；
 
$>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log owner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000M
 
解決步驟：
尋找資料庫的UNDO資料表空間名
 
SQL> select name from v$tablespace;
 
檢查資料庫UNDO資料表空間佔用空間情況以及資料檔案存放位置；
SQL>select file_name, bytes/1024/1024 from dba_data_files;
 
查看復原段的使用方式，哪個使用者正在使用復原段的資源，如果有使用者最好更換時間（特別是生產環境）。
select s.username, u.name, d.tablespace_name
from v$transaction t,v$rollstat r, v$rollname u, v$session s, dba_rollback_segs d
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn and r.usn=d.SEGMENT_ID order by s.username;
 
檢查UNDO Segment狀態
select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
 from v$rollstat order by rssize;
 
建立新的UNDO資料表空間，並設定自動擴充參數；
create undo tablespace undotbs2 datafile '/oradata/oradata/ddptest/UNDOTBS2.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;
 
動態更改spfile設定檔；
alter system set undo_tablespace=undotbs2 scope=both;
 
等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE；
select segment_name, tablespace_name, status from dba_rollback_segs;
 
再執行看UNDO資料表空間所有UNDO SEGMENT ONLINE；
select segment_name, tablespace_name, status from dba_rollback_segs;
 
刪除原有的UNDO資料表空間；
drop tablespace undotbs2 including contents;
 
確認刪除是否成功；
select name from v$tablespace;
 
冊除原UNDO資料表空間的資料檔案，其檔案名稱為步驟中執行的結果。
#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf
 
-- 查看事務對應的復原段
select a.name, b.xacts, c.sid, c.serial# ,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
 
-- 查看事務佔用undo 空間
col username for a16
col oSUSEr for a16
SELECT t.used_ublk,
      s.username,
      s.sid,
      s.serial#,
      pr.PID,
      s.OSUSER,
      s.MACHINE,
      s.PROGRAM,
      rs.segment_id,
      r.usn,
      rs.segment_name,     
      r.rssize/1024/1024,
      sq.sql_text
  FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs, v$sqltext sq, v$process pr
 WHERE s.saddr = t.ses_addr
  AND t.xidusn = r.usn
  AND rs.segment_id = t.xidusn
  AND s.sql_address=sq.address
  AND s.sql_hash_value = sq.hash_value
  AND s.PADDR=pr.ADDR
ORDER BY t.used_ublk DESC, sq.PIECE;

Oracle Undo 鏡像資料探究

Oracle 復原(ROLLBACK)和撤銷(Undo)

Undo 資料表空間損壞導致無法open

Undo資料表空間失敗的處理方法

Oracle Undo資料表空間重建與恢複