Automatic Undo Retention
Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:

For an AUTOEXTEND undo tablespace, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows. In addition, when there is adequate free space, the tuned retention period does not go below the value of the UNDO_RETENTION initialization parameter.

For a fixed size undo tablespace, the database tunes for the maximum possible undo retention. This means always providing the longest possible retention period while avoiding out-of-space conditions and near out-of-space conditions in the undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.

Automatic tuning of undo retention is not supported for LOBs. The tuned retention value for LOB columns is set to the value of the UNDO_RETENTION parameter.

For fixed size and AUTOEXTEND undo tablespaces of equal size, depending on the queries that you run, the tuning method used in fixed size tablespaces tends to provide a longer retention period. This enables flashback operations to flash back farther in time, and maximizes the amount of undo data available for long-running queries

[@more@]

--=================================
--第一种情况开启undo自动调整，undo datafile可以自动扩展
session 1:
SQL> col value format a20
SQL> SELECT nam.ksppinm NAME, val.ksppstvl VALUE
2 FROM x$ksppi nam, x$ksppsv val
3 WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%_undo_autotune%'
4 ORDER BY 1;

NAME VALUE
-------------------- --------------------
_undo_autotune TRUE
SQL> select * from t;

ID NAME
---------- ----------
1 b

SQL> variable i refcursor;
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> exec open :i for select * from t;

PL/SQL 过程已成功完成。
--=============================
session 2:
SQL> declare
2 i number :=0;
3 begin
4 loop
5 update t set name='a';
6 i:=i+1;
7 if mod(i,100)=0 then
8 commit;
9 end if ;
10 end loop;
11 end;
/
--==============================
session 1:
SQL> col file_name format a40
SQL> select file_name,bytes,autoextensible from dba_data_files where file_id=5;

FILE_NAME BYTES AUT
---------------------------------------- ---------- ---
E:APPWCHORADATATESTUNDOTBS02.DBF 230686720 YES

SQL> select file_name,bytes,autoextensible from dba_data_files where file_id=5;

FILE_NAME BYTES AUT
---------------------------------------- ---------- ---
E:APPWCHORADATATESTUNDOTBS02.DBF 230686720 YES

SQL> select file_name,bytes,autoextensible from dba_data_files where file_id=5;

FILE_NAME BYTES AUT
---------------------------------------- ---------- ---
E:APPWCHORADATATESTUNDOTBS02.DBF 241172480 YES

SQL> print i;

ID NAME
---------- ----------
1 b
SQL> select begin_time,end_time,undoblks,maxquerylen,tuned_undoretention from v$
undostat;

BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN TUNED_UNDORETENTION
-------------- -------------- ---------- ----------- -------------------
26-7月 -10 26-7月 -10 11806 647 2115
26-7月 -10 26-7月 -10 21899 909 1750
26-7月 -10 26-7月 -10 50 306 1147
--这段时间内v$undostat中TUNED_UNDORETENTION的值始终在变化，并且都大于900
SQL>
--=====================
--第二种情况开启undo自动调整，undo datafile不可以自动扩展
SQL> create undo tablespace undotbs1 datafile 'E:APPWCHORADATATESTUNDOTBS01
.DBF' size 1m;

表空间已创建。

SQL> alter system set undo_tablespace=undotbs1;

系统已更改。

SQL> select file_name,bytes,autoextensible from dba_data_files where file_id=3;

FILE_NAME BYTES AUT
---------------------------------------- ---------- ---
E:APPWCHORADATATESTUNDOTBS01.DBF 1048576 NO

SQL> select * from t;

ID NAME
---------- --------------------
1 a

SQL> exec open :i for select *from t;

PL/SQL 过程已成功完成。

SQL> set time on
16:35:32 SQL> exec open :i for select *from t;

PL/SQL 过程已成功完成。
--=======================
session 2:
SQL> declare
2 i number :=0;
3 begin
4 loop
5 update t set name='a';
6 i:=i+1;
7 if mod(i,100)=0 then
8 commit;
9 end if ;
10 end loop;
11 end;
12 /

--=======================
session 1:

16:35:34 SQL> print i;
ERROR:
ORA-01555: 快照过旧: 回退段号 1 (名称为 "_SYSSMU1_1280133205$") 过小

未选定行
--这里显然没有保留够900秒，undo里的内容就被覆盖了，同时v$undostat中TUNED_UNDORETENTION的值只有4，显然比900秒小了很多
16:36:45 SQL> select begin_time,end_time,undoblks,maxquerylen,tuned_undoretentio
n from v$undostat;

BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN TUNED_UNDORETENTION
-------------- -------------- ---------- ----------- -------------------
26-7月 -10 26-7月 -10 9998 320 4
26-7月 -10 26-7月 -10 12 917 1760
26-7月 -10 26-7月 -10 11806 647 1490
26-7月 -10 26-7月 -10 21899 909 1750
26-7月 -10 26-7月 -10 50 306 1147

16:37:24 SQL>
--======================
--第三种情况关闭undo自动调整，undo datafile不可以自动扩展
16:37:24 SQL> alter system set "_undo_autotune"=false;

系统已更改。

16:45:00 SQL> SELECT nam.ksppinm NAME, val.ksppstvl VALUE
16:45:13 2 FROM x$ksppi nam, x$ksppsv val
16:45:13 3 WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%_undo_autotune
%'
16:45:13 4 ORDER BY 1;

NAME VALUE
-------------------- --------------------
_undo_autotune FALSE

16:45:14 SQL> select * from t;

ID NAME
---------- --------------------
1 a

16:45:27 SQL> exec open :i for select * from t;

PL/SQL 过程已成功完成。
--========================
session 2:
declare
i number :=0;
begin
loop
update t set name='b';
i:=i+1;
if mod(i,100)=0 then
commit;
end if ;
end loop;
end;
/
--=======================
session 1:
16:45:59 SQL> print i;
ERROR:
ORA-01555: 快照过旧: 回退段号 7 (名称为 "_SYSSMU7_1280133205$") 过小

未选定行

16:47:45 SQL> select begin_time,end_time,undoblks,maxquerylen,tuned_undoretentio
n from v$undostat;

BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN TUNED_UNDORETENTION
-------------- -------------- ---------- ----------- -------------------
26-7月 -10 26-7月 -10 9542 624 7
26-7月 -10 26-7月 -10 10003 320 5
26-7月 -10 26-7月 -10 12 917 1760
26-7月 -10 26-7月 -10 11806 647 1490
26-7月 -10 26-7月 -10 21899 909 1750
26-7月 -10 26-7月 -10 50 306 1147

已选择6行。

16:48:32 SQL>
--=======================================
--第四种情况关闭undo自动调整，undo datafile可以自动扩展
16:48:32 SQL> alter system set undo_tablespace=undotbs2;

系统已更改。

16:50:17 SQL> select file_name,bytes,autoextensible from dba_data_files where f
ile_id=5;