Oracle 10g的UNDO_RETENTION管理增强：
在AUM模式下，UNDO_RETENTION参数用以控制事务提交以后Undo保留的时间。该参数以秒为单位，9iR1初始值为900秒，在Oracle 9iR2增加为10800秒。但是这是一个NO Guaranteed的限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。
从Oracle 10g开始，如果设置UNDO_RETENTION为0，那么Oracle启动自动调整以满足最长运行查询的需要。当然如果空间不足，那么Oracle满足最大允许的长时间查询。而不再需要用户调整。当设置undo_retention为0后，在警告日志文件中可以看到“Autotune of undo retention is turned on.”信息。
这个新特性的引入伴随着几个新的隐含初始化参数，主要的参数有2个：
sys@TQGZS> select ksppinm,ksppdesc
2 from x$ksppi where ksppinm like '%&var%';
Enter value for var: undo_autotune
old 2: from x$ksppi where ksppinm like '%&var%'
new 2: from x$ksppi where ksppinm like '%undo_autotune%'
KSPPINM KSPPDESC
-------------------- --------------------------------------------------
_undo_autotune enable auto tuning of undo_retention
sys@TQGZS> /
Enter value for var: collect_undo_stats
old 2: from x$ksppi where ksppinm like '%&var%'
new 2: from x$ksppi where ksppinm like '%collect_undo_stats%'
KSPPINM KSPPDESC
-------------------- --------------------------------------------------
_collect_undo_stats Collect Statistics v$undostat
这两个参数缺省都是打开的。
很多时候我们希望前镜像数据能够尽量，而不是被覆盖，Oracle 10g对于Undo增加了Guarantee控制，也就是说，用户可以指定Undo表空间必须满足UNDO_RETENTION限制。通过如下命令可以修改Undo表空间的新属性：
alter tablespace undotbs1 retention guarantee|noguarantee;
设置期望的保留时间，修改Undo表空间属性，就可以使Undo表空间运行在Guarantee模式：
sys@TQGZS> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
2 where tablespace_name like 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDO_SMALL UNDO NOGUARANTEE
sys@TQGZS> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
2 where tablespace_name like 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO GUARANTEE
UNDO_SMALL UNDO NOGUARANTEE
将Undo表空间自动扩展属性取消进行如下测试：
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
2 where tablespace_name like 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDO_SMALL UNDO NOGUARANTEE
sys@TQGZS> select name,bytes/1024/1024 MB from v$datafile where name like '%undo%';
NAME MB
-------------------------------------------------- ----------
/u01/oracle/oradata/tqgzs/undotbs01.dbf 424
/u01/oracle/oradata/tqgzs/undo_small.dbf 2
sys@TQGZS> alter database datafile '/u01/oracle/oradata/tqgzs/undo_small.dbf' autoextend off;
Database altered.
sys@TQGZS> alter system set undo_tablespace = undo_small;
System altered.
sys@TQGZS> alter tablespace undo_small retention guarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
2 where tablespace_name like 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDO_SMALL UNDO GUARANTEE
sys@TQGZS> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO_SMALL
尝试循环小批量删除数据，在GUARANTEE设置下，很快出现ORA-30036错误：
sys@TQGZS> conn tq/tq
Connected.
tq@TQGZS> select count(*) from test;
COUNT(*)
----------
1635808
tq@TQGZS> begin
2 for i in 1 .. 1000 loop
3 delete from test where rownum <1001;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_SMALL'
ORA-06512: at line 3
tq@TQGZS> select count(*) from test;
COUNT(*)
----------
1633808
而在修改了Undo表空间retention属性后，删除可以顺利完成：
tq@TQGZS> conn / as sysdba
Connected.
sys@TQGZS> alter tablespace undo_small retention noguarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
2 where tablespace_name like 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDO_SMALL UNDO NOGUARANTEE
sys@TQGZS> conn tq/tq
Connected.
tq@TQGZS> begin
2 for i in 1 .. 1000 loop
3 delete from test where rownum <1001;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
这就是GUARANTEE与NOGUARANTEE的不同。
- The End -