This content has been marked as final. Show 5 replies
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.
Shrini, I read the documentation, check the relevant line from doc "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" , in my doubt case, we have fixed size undo tablespace and undo retention is not guaranteed, then as seen above oracle will ignore UNDO_RETENTION parameter, if it will ignore this parameter what is the necessity of setting up of this parameter when undo tablespace is fixed size and undo retention is not guaranteed.
Kindly let me know your inputs. Thanks in advance
If you have a fixed UNDO tablespace size, Oracle will do its best to retain undo for at least the amount of time specified by this parameter.
If you do not explicitly set this parameter, it defaults to 900 seconds (15 min) in 11gR2.
So if you have a small UNDO tablespace, you are likely to run into ORA-01555 (snapshot too old) errors - if you have a large UNDO tablespace, then undo retention will likely exceed this parameter value (although there are no guarantees).
Oracle needs a value to be set to act as a "barometer" - default is 15 min