Oracle 10g及后续版本较以前版本有一新特性即自动调整undo retention时间，大大简便了管理，对于自动扩展(autoextend on)的undo表空间，参数undo_retention设置成为Oracle自动调节undo retention的最低阀值。对于非自动扩展(autoextend off)，非guarantee 的undo 表空间，Oracle会根据undo表空间大小和v$undostat的历史信息(是否统计undo信息是由隐含参数_collect_undo_stats决定的，默认情况为TRUE)最大可能性保留undo信息。

查看undo数据文件是否自动扩展，undo表空间是否处于gurantee状态。

引用

SQL> select AUTOEXTENSIBLE,RETENTION

2  from dba_tablespaces,dba_data_files

3  where dba_data_files.TABLESPACE_NAME=dba_tablespaces.TABLESPACE_NAME

4  and dba_data_files.TABLESPACE_NAME='UNDOTBS1'

5  ;

AUT RETENTION

--- -----------

YES NOGUARANTEE

当然这一特性是由隐含参数_undo_autotune控制的，默认情况下设置为TRUE，部分特殊情况下会将其设为FALSE，如startup upgrade。

引用

SQL> set linesize 120

SQL> col name for a40 trunc

SQL> col value for a20

SQL> col pdesc for a50 trunc

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC

2    FROM SYS.x$ksppi x, SYS.x$ksppcv y

3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';

Enter value for par: undo_autotune

old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'

new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%undo_autotune%'

NAME                                     VALUE                PDESC

---------------------------------------- -------------------- --------------------------------------------------

_undo_autotune                           TRUE                 enable auto tuning of undo_retention

以下为startup upgrade alert启动日志：

引用

ALTER SYSTEM enable restricted session;

Thu Jan  7 10:17:06 2010

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;

Autotune of undo retention is turned off.

Thu Jan  7 10:17:06 2010

ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;

Thu Jan  7 10:17:06 2010

ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;

Thu Jan  7 10:17:06 2010

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;

Resource Manager disabled during database migration: plan '' not set

Thu Jan  7 10:17:06 2010

ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: ALTER DATABASE OPEN MIGRATE

以下为最小化参数启动Oracle日志：

引用

Sat Jan  9 13:58:08 2010

Adjusting the default value of parameter parallel_max_servers

from 40 to 25 due to the value of parameter processes (40)

Sat Jan  9 13:58:08 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Sat Jan  9 13:58:19 2010

Adjusting the default value of parameter parallel_max_servers

from 40 to 25 due to the value of parameter processes (40)

Sat Jan  9 13:58:19 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Sat Jan  9 13:59:52 2010

Adjusting the default value of parameter parallel_max_servers

from 40 to 25 due to the value of parameter processes (40)

Sat Jan  9 13:59:52 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Sat Jan  9 14:01:25 2010

Adjusting the default value of parameter parallel_max_servers

from 40 to 25 due to the value of parameter processes (40)

Sat Jan  9 14:01:25 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instance monitoring created

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as /ora10g/oracle/product/10.2.0/db_1/dbs/arch

Autotune of undo retention is turned off. LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.3.0. System parameters with non-default values:   db_name                  = zhoul PMON started with pid=2, OS id=32133 PSP0 started with pid=3, OS id=32135 MMAN started with pid=4, OS id=32137 DBW0 started with pid=5, OS id=32139 LGWR started with pid=6, OS id=32141 CKPT started with pid=7, OS id=32143 SMON started with pid=8, OS id=32145 RECO started with pid=9, OS id=32147 MMON started with pid=10, OS id=32149 MMNL started with pid=11, OS id=32151