解释Undo Size = Undo_retention * UPS
最近Oracle8i频频在eXP的时候发生ora-1555,才深感oracle9i的undo 表空间自动治理模式好处;
oracle9i使用参数undo_retention 设置undo 的保留时间;
SQL> show parameters undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
通过在undo segment header中引入extent commit time,记录每个区间涉及到的事务最近一次commit的时间。
Extent Map
-----------------------------------------------------------------
0x0080005a length: 7
0x00800061 length: 8
0x00800989 length: 128
0x00800a89 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1113807175
Extent Number:1 Commit Time: 1113809908
Extent Number:2 Commit Time: 1113965650
Extent Number:3 Commit Time: 1114067240
如何设置undo表空间的大小才能保证undo_retention ?使用公式
Undo Size = Undo_retention * UPS
UPS是undo block per second, 我们可以通过V$UNDOSTAT. UNDOBLKS获得 .
SQL> select avg(undoblks)/(10*60) UPS from v$undostat;
UPS
-------------
0.03
则undo_retention=10800,至少需要 10800*0.03=324个数据块。