Automatic tuning of Undo Retention

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

1. If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.
2. If the undo tablespace is configured with the autoextend option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.


Automatic tuning of undo retention is not supported for lobs. This is because undo information for lobs is stored in the segment itself and not in the undo tablespace. For lobs, the database attempts to honor the minimum undo retention period specified by undo_retention. However, if space becomes low, unexpired lob undo information may be overwritten.

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

You can determine the current retention period by querying the tuned_undoretention column of the v$undostat view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (beyond 4 days, the data is available in the dba_hist_undostat view.) Tuned_undoretention is given in seconds.

Sql> select to_char(begin_time, ‘dd-mon-rr hh24:mi’) begin_time,

To_char(end_time, ‘dd-mon-rr hh24:mi’) end_time, tuned_undoretention

From v$undostat order by end_time;

Subscribe to our youtube channel to get new updates..!

Begin_time                              end_time               tuned_undoretention

————————          ——————–      ———————————–

04-feb-05 00:01                     04-feb-05 00:11                  12100                                    

07-feb-05 23:21                      07-feb-05 23:31                  86700

07-feb-05 23:31                      07-feb-05 23:41                  86700

07-feb-05 23:41                      07-feb-05 23:51                  86700

07-feb-05 23:51                      07-feb-05 23:52                  86700

576 rows selected.

Undo retention tuning and alert thresholds:

For a fixed size undo tablespace, the database calculates the maximum undo retention period based on database statistics and on the size of the undo tablespace. For optimal undo management, rather than tuning based on 100% of the tablespace size, the database tunes the undo retention period based on 85% of the tablespace size, or on the warning alert threshold percentage for space used, whichever is lower. (the warning alert threshold defaults to 85%, but can be changed.) Therefore, if you set the warning alert threshold of the undo tablespace below 85%, this may reduce the tuned length of the undo retention period.

Checkout Oracle DBA Interview Questions

Setting the Undo Retention period

You set the undo retention period by setting the undo_retention initialization parameter. This parameter specifies the desired minimum undo retention period in seconds. The current undo retention period may be automatically tuned to be greater than undo_retention, or, unless retention guarantee is enabled, less than undo_retention if space is low.

To set the undo retention period:

Do one of the following:

  • Set undo_retention in the initialization parameter file.
  • Undo_retention = 1800
  • Change undo_retention at any time using the alter system statement:
  • Alter system set undo_retention = 2400;

The effect of an undo_retention parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.

For Further Posts please visit our BLOG

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!