Oracle10g的UNDO_RETENTION自動化管理增強
2024-08-29 13:37:19
供稿：網友
在AUM模式下，我們知道UNDO_RETENTION參數用以控制事務提交以后undo信息保留的時間。該參數以秒為單位，9iR1初始值為900秒，在Oracle9iR2增加為10800秒。但是這是一個NO Guaranteed的限制。
也就是說，假如有其他事務需要回滾空間，而空間出現不足時，這些信息仍然會被覆蓋。
很多時候這是不希望看到的。
從Oracle10g開始，假如你設置UNDO_RETENTION為0，那么Oracle啟用自動調整以滿足最長運行查詢的需要。當然假如空間不足，那么Oracle滿足最大答應的長時間查詢。而不再需要用戶手工調整。
同時Oracle增加了Guarantee控制，也就是說，你可以指定UNDO表空間必須滿足UNDO_RETENTION的限制。
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered
SQL> alter tablespace undotbs1 retention noguarantee;
Tablespace altered
在DBA_TABLESPACES視圖中增加了RETENTION字段用以描述該選項:
SQL> select tablespace_name,contents,retention from dba_tablespaces;
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
SYSTEM PERMANENT NOT APPLY
UNDOTBS1 UNDO NOGUARANTEE
SYSAUX PERMANENT NOT APPLY
TEMP TEMPORARY NOT APPLY
USERS PERMANENT NOT APPLY
EYGLE PERMANENT NOT APPLY
TEST PERMANENT NOT APPLY
ITPUB PERMANENT NOT APPLY
TRANS PERMANENT NOT APPLY
BIGTBS PERMANENT NOT APPLY
TEMP2 TEMPORARY NOT APPLY
TEMP03 TEMPORARY NOT APPLY
DFMBRC PERMANENT NOT APPLY
T2K PERMANENT NOT APPLY
14 rows selected
在Oracle官方文檔上這樣解釋:
RETENTION Undo tablespace retention:
GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE
A RETENTION value of GUARANTEE indicates that uneXPired undo in all undo segments in the undo tablespace should be retained even if it means that forward going Operations that need to generate undo in those segments fail.
NOGUARANTEE - Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE
NOT APPLY - Tablespace is not an undo tablespace.
