My Oracle Support Banner

10g NEW FEATURE on AUTOMATIC UNDO RETENTION (Doc ID 240746.1)

Last updated on NOVEMBER 23, 2023

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Up to 9i we don’t have that much control over ORA-01555 errors. But from 10g onward we have this new feature, automatic tuning of undo_retention, which help control ORA-01555 situation better.

Scope

 UNDO_MANAGEMENT=AUTO /* Defaults to MANUAL in 10gR2*/

How it works (Step to Test):
=============================
If UNDO_MANAGEMENT=AUTO (no matter the setting for UNDO_RETENTION) automatic undo tuning will be used.MMON will calculate the MAXQUERYLEN every 30 sec interval. Based upon the MAXQUERYLEN, MMON decides theTUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

Note: 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

 .

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.