ORACLE 變量之 UNDO_RETENTION

官網：

Property	Description
Parameter type	Integer
Default value	900
Modifiable	ALTER SYSTEM
Range of values	to 231 - 1
Oracle RAC	Oracle recommends that multiple instances have the same value.

UNDO_RETENTION

 specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores 

UNDO_RETENTION

 unless retention guarantee is enabled.  

大小固定的undo 表空間，系統會根據該表空間的大小和使用曆史情況來自動調整最大的可能保留undo 期限，如果undo表空間的retention guarantee 無效，那麼會忽略UNDO_RETENTION 變量；

關于undo表空間的retention  guarantee設定：

{

Changing Undo Data Retention: Examples The following statement changes the undo data retention for tablespace 

undots1

 to normal undo data behavior:

ALTER TABLESPACE undots1
  RETENTION NOGUARANTEE;
      

The following statement changes the undo data retention for tablespace 

undots1

 to behavior that preserves unexpired undo data:

ALTER TABLESPACE undots1
  RETENTION GUARANTEE;      

}

The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the 

UNDO_RETENTION

 parameter.

The 

UNDO_RETENTION

 parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the 

TUNED_UNDORETENTION

column of the 

V$UNDOSTAT

 dynamic performance view.

See Also:

• Oracle Database SQL Language Reference for more information about creating undo tablespaces
• Oracle Database Administrator's Guide for more information about managing undo data

OCP題庫中考察此知識點的題：

一.In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a value?

A.when the data file of the undo tablespace is autoextensible

B.when there are more than one undo tablespaces available in the database

C.when the undo tablespace is of a fixed size and retention guarantee is not enabled

D.when the undo tablespace is autoextensible and retention guarantee is not enabled

The answer is C

二.View the Exhibit and examine the attributes of an undo tablespace.

In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional

hour that performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives the following error:

ORA-01555: snapshot too old

What could be the reason for this error?

A.The query is unable to get a read-consistent image.

B.There is not enough space in Flash Recovery Area.

C.There is not enough free space in the flashback archive.

D.The query is unable to place data blocks in undo tablespace.

因為undo_retention 預設是15分鐘，是以到15分鐘後才會出現此錯誤；

The answer is A    （不能得到一緻性讀）