Understanding UNDO Configuration and RETENTION GUARANTEE in Oracle 19c

Managing UNDO correctly in Oracle Database 19c is critical for transaction consistency, read consistency, flashback operations, and overall database stability.

For more information: Oracle Flashback Technology

In this article, we’ll walk through:

• Understanding the current UNDO configuration
• What UNDO_RETENTION really means
• The difference between NOGUARANTEE and GUARANTEE
• When and why to enable RETENTION GUARANTEE

1. Checking the Current UNDO Configuration

First, we check the UNDO parameters:

SQL> show parameter undo

Output:

Parameter	Value
temp_undo_enabled	FALSE
undo_management	AUTO
undo_retention	900
undo_tablespace	UNDOTBS1

Interpretation

• UNDO_MANAGEMENT = AUTO
  The database automatically manages undo segments (recommended mode).
• UNDO_RETENTION = 900
  Oracle tries to retain undo data for 900 seconds (15 minutes).
• UNDO_TABLESPACE = UNDOTBS1
  The active undo tablespace is UNDOTBS1.
• TEMP_UNDO_ENABLED = FALSE
  Temporary undo is not enabled (used mainly with Global Temporary Tables).

2. Checking UNDO Tablespace Retention Mode

Next, we check whether the UNDO tablespace guarantees retention:

SQL> select tablespace_name, retention 
     from dba_tablespaces 
     where tablespace_name='UNDOTBS1';

Output:

TABLESPACE_NAME	RETENTION
UNDOTBS1	NOGUARANTEE

What Does NOGUARANTEE Mean?

With NOGUARANTEE (default mode):

• Oracle tries to respect UNDO_RETENTION
• But if space pressure occurs, Oracle can overwrite unexpired undo
• This may cause:
  • ORA-01555: snapshot too old
  • Flashback query failures

In other words, retention is best effort, not enforced.

3. Enabling RETENTION GUARANTEE

To force Oracle to preserve undo for the full retention period:

SQL> alter tablespace undotbs1 retention guarantee;

After execution:

SQL> select tablespace_name, retention from dba_tablespaces where tablespace_name='UNDOTBS1';

Output:

TABLESPACE_NAME	RETENTION
UNDOTBS1	GUARANTEE

Now the tablespace is in GUARANTEE mode.

4. What Does RETENTION GUARANTEE Do?

When RETENTION GUARANTEE is enabled:

• Oracle will NOT overwrite undo before UNDO_RETENTION expires
• Even if active transactions need space
• If space runs out → DML operations may fail with:

ORA-30036: unable to extend segment by ... in undo tablespace

This is the major difference:

Mode	Behavior
NOGUARANTEE	Overwrites undo if needed
GUARANTEE	Never overwrites before retention period

5. When Should You Use RETENTION GUARANTEE?

Recommended Use Cases

• Flashback Query
• Flashback Table
• Flashback Database
• Long-running reports
• Logical standby environments
• Systems requiring guaranteed read consistency

Not Recommended When

• Undo tablespace is small
• Heavy OLTP with high DML volume
• Storage is limited

For more information: Oracle Flashback Technology