053试题 377/379 - undo retention

题目：

377.AUM has been retaining about 15 minutes worth of undo. You want to double the retention period, but
not at the expense of new transactions failing. You decide to alter the system to set the parameter
UNDO_RETENTION=18000. However, AUM still retains only about 15 minutes worth of undo. What is the
problem? (Choose the best answer.)
A. You need to alter the undo tablespace to add the RETENTION GUARANTEE setting.
B. You need to increase the size of the undo tablespace.
C. The undo tablespace is not set to auto-extend.
D. You need to alter the Recycle Bin to add the RETENTION GUARANTEE setting.
参考答案 C
undo表空间是自动undo。undo_retention设置是15Min。 想设置为30MIn。设置了undo_retention是18000.但是发现retention还是15Min。原因？
undo表空间没有设置自动扩展。选择C 。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN10180

About the Undo Retention Period

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired and its space is available to be overwritten by new transactions. Old undo information with an age that is less than the current undo retention period is said to be unexpired and is retained for consistent read and Oracle Flashback operations.

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can optionally specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The exact impact this parameter on undo retention is as follows:

• The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See "Automatic Tuning of Undo Retention" for more information.

• For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.

Automatic Tuning of Undo Retention

Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.

• If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate Oracle Flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.

• If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.

  If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:

  • DML could fail because there is not enough space to accommodate undo for new transactions.

  • Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

  See "Sizing a Fixed-Size Undo Tablespace" for more information.

END

题目：

379.Users notify you that their application is failing every time they try to add new records. Because of
poor application design, the actual ORA error message is unavailable. What might be the problem? (Choose the best answers.)
A. The application user has exceeded their undo quota.
B. The FLASHBACK GUARANTEE option is set on the undo tablespace.
C. The table is currently being queried by a Flashback Transaction Query operation.
D. The table is currently being queried by a Flashback Versions Query operation.
E. The RETENTION GUARANTEE option is set on the undo tablespace.
 
参考答案 AE (best answer ,选择A )
解析
当添加记录的时候，发现应用程序报错。耗光了undo quota。选择A  。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11477

Establishing User Quotas for Undo Space

The Oracle Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group).

You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.

When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.

END