在Oracle 10g數(shù)據(jù)庫的應(yīng)用中，出現(xiàn)了UNDO表空間過大導致磁盤空間不足而崩潰的現(xiàn)象。對此問題進行分析后，總結(jié)了出現(xiàn)該問題的原因主要有以下兩點：

1. 有較大的事務(wù)量讓Oracle Undo自動擴展，產(chǎn)生過度占用磁盤空間的情況；

2. 有較大事務(wù)沒有收縮或者沒有提交所導制；

說明：本問題在Oracle系統(tǒng)管理中屬于比較正常的一現(xiàn)象，日常維護多注意對磁盤空間的監(jiān)控。

Oracle 10g 有自動Automatic Undo Retention Tuning 這個特性。設(shè)置的 undo_retention 參數(shù)只是一個指導值，缺省值900秒,，Oracle 會自動調(diào)整 Undo (會跨過 undo_retention 設(shè)定的時間) 來保證不會出現(xiàn) Ora-1555 錯誤.。通過查詢V$UNDOSTAT（該視圖記錄4天以內(nèi)的UNDO表空間使用情況，超過4天可以查詢DBA_HIST_UNDOSTAT視圖）的tuned_undoretention （該字段在10G版本才有，9I是沒有的）字段可以得到Oracle 根據(jù)事務(wù)量（如果是文件不可擴展，則會考慮剩余空間）采樣后的自動計算出***的 retenton 時間.。

1)查詢retention值

show parameter undo_retention

查詢自動計算出***的retenton 時間

select tuned_undoretention, maxquerylen, maxqueryid from v$undostat;

2）更改retention值

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

這樣對于一個事務(wù)量分布不均勻的數(shù)據(jù)庫來說,，就會引發(fā)潛在的問題--在批處理的時候可能 Undo 會用光， 而且這個狀態(tài)將一直持續(xù)， 不會釋放。

如何取消10g的auto UNDO Retention Tuning，有如下三種方法：

(1)10.2.0.2/10.2.0.3有相應(yīng)的patch，這個bug在10.2.0.4中已經(jīng)修復，建議找時間停機打patch.

(2)設(shè)置隱含參數(shù)_smu_debug_mode=33554432，將tuned_undoretention取值算法修正為max(maxquerylen secs + 300,undo_retention )，不建議使用SQL> Alter system set "_smu_debug_mode" = 33554432;

(3)設(shè)置隱含參數(shù)_undo_autotune=false，關(guān)閉自動undo retention調(diào)整特性，不建議使用SQL> Alter system set "_undo_autotune" = false;from metalink 420525.1： Automatic Tuning of Undo_retention Causes Space Problems.

解決步驟：

1. 啟動SQLPLUS，并用sys登陸到數(shù)據(jù)庫。

#su - oracle  
$>sqlplus / as sysdba 

2. 查找數(shù)據(jù)庫的UNDO表空間名,確定當前例程正在使用的UNDO表空間：Show parameter undo_tablespace。

3. 確認UNDO表空間；

SQL> select name from v$tablespace;  
NAME  
------------------------------  
.......  
UNDOTBS1 

4. 檢查數(shù)據(jù)庫UNDO表空間占用空間情況以及數(shù)據(jù)文件存放位置；

SQL>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';

5. 查看回滾段的使用情況，哪個用戶正在使用回滾段的資源，如果有用戶***更換時間（特別是生產(chǎn)環(huán)境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s  
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; 

6. 檢查UNDO Segment狀態(tài)；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize; 

7. 創(chuàng)建新的UNDO表空間，并設(shè)置自動擴展參數(shù)；

SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend on next 50m maxsize 5000m; 
Tablespace created.

8. 動態(tài)更改spfile配置文件；

SQL> alter system set undo_tablespace=undotbs2 scope=both;  
System altered. 

9. 等待原UNDO表空間所有UNDO SEGMENT OFFLINE；

select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from   v$rollstat order by rssize; 

10. 再執(zhí)行看UNDO表空間所有UNDO SEGMENT ONLINE；

select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize; 

11. 刪除原有的UNDO表空間；

SQL> drop tablespace undotbs1 including contents;  
Tablespace dropped. 

12. 確認刪除是否成功；

SQL> select name from v$tablespace;  
NAME  
------------------------------  
.......  
UNDOTBS2  
12 rows selected. 

13. 更新pfile

SQL> create pfile from spfile;  
File created. 

14. 冊除原UNDO表空間的數(shù)據(jù)文件，其文件名為步驟中執(zhí)行的結(jié)果。

#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf 

關(guān)于Oracle 10g UNDO表空間過大導致磁盤空間不足的解決方法就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

1. SQL Server使用UNION代替OR提升查詢性能的實例
2. Oracle數(shù)據(jù)庫分析函數(shù)應(yīng)用實例之查找狀態(tài)全為1的ID
3. SQL Server表中數(shù)據(jù)按條件批量導出為多個Excel文件
4. Oracle數(shù)據(jù)庫中如果存在表就刪除不存在就創(chuàng)建的實例
5. Oracle handbook系列之虛擬專用數(shù)據(jù)庫VPD的使用詳解