What is UNDO_RETENTION: How It Works and Why It Matter

The UNDO_RETENTION parameter in Oracle is an important setting that determines how long undo data (old versions of modified data) should be retained before it’s overwritten. However, it’s often misunderstood or seen as ineffective in solving issues like ORA-01555. This post explains how UNDO_RETENTION works, when it matters, and what you can do to manage undo space.

What is UNDO_RETENTION?

UNDO_RETENTION specifies how long Oracle should retain undo data before it can be overwritten. It helps ensure that undo information stays available for queries and transactions that need it. However, it only works effectively under certain conditions, and it’s important to understand how it interacts with your database setup.

Key Points to Know:

1. Fixed-Size Undo Tablespace:
   • If your undo tablespace has a fixed size, the UNDO_RETENTION setting is ignored.
   • In this case, Oracle automatically manages the undo retention period based on system activity and the size of the undo tablespace.
2. Autoextend Undo Tablespace:
   • If your undo tablespace is set to autoextend, Oracle will try to respect the UNDO_RETENTION setting.
   • When the tablespace runs low on space, it will autoextend instead of deleting older undo data.
   • However, if the maximum size of the undo tablespace is reached, Oracle may begin overwriting undo data that hasn’t expired yet.
   • The UNDOTBS1 tablespace created by the Database Configuration Assistant (DBCA) is typically autoextending by default.

What to Do If Your Undo Space Grows Too Much?

If you notice that the undo tablespace is growing more than expected, and simply adjusting the UNDO_RETENTION setting doesn’t seem to help, here’s a quick solution you can try.

• Temporary Fix: You can reduce the UNDO_RETENTION value temporarily in memory. This will cause most of the undo segments to expire, freeing up space. Once expired, Oracle will reclaim the space and stop the undo tablespace from growing.

How to Check Your Undo Usage?

To check the undo retention period and other related details, you can use Oracle’s built-in functions. Here are some examples:

-- Get the best possible undo retention over the last 7 days
SQL> select dbms_undo_adv.best_possible_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;

UNDO_RETENTION
--------------
       1583948

1583948 seconds (~18.3 days).

-- Get the longest running query’s undo retention over the last 7 days
SQL> select dbms_undo_adv.longest_query(sysdate-7, sysdate) UNDO_RETENTION from dual;

UNDO_RETENTION
--------------
          7182

7182 seconds (~2 hours).

-- Get the required undo retention over the last 7 days
SQL> select dbms_undo_adv.required_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;

UNDO_RETENTION
--------------
          7182

7182 seconds (~2 hours).

-- Get the undo retention needed for RBU migration over the last 7 days
SQL> select dbms_undo_adv.rbu_migration(sysdate-7, sysdate) UNDO_RETENTION from dual;

UNDO_RETENTION
--------------
          1600

1600 seconds (~26.6 minutes).

Each of these queries helps you determine different aspects of UNDO_RETENTION for your system. These values are all in seconds, indicating how long undo information needs to be retained.

Understanding the UNDO_RETENTION Guarantee

The effectiveness of UNDO_RETENTION depends on the type of undo tablespace management you are using. Here’s a simple matrix that shows how UNDO_RETENTION works under different conditions:

Space Type	No Guarantee	With Guarantee
Fixed-Size	Ignored	Guaranteed until no free space is left
Autoextend	Honored	Guaranteed until MAXSIZE is reached

• No Guarantee: For a fixed-size undo tablespace, UNDO_RETENTION is ignored. Oracle does its best to retain undo data but may overwrite it when space runs out.
• With Guarantee: For an autoextending undo tablespace, UNDO_RETENTION is respected until the maximum size is reached. Once the limit is hit, Oracle may start overwriting unexpired undo data.

Conclusion

In summary, UNDO_RETENTION is an essential parameter for managing undo data in Oracle. It can help ensure undo information is available when needed, but its effectiveness depends on the undo tablespace configuration (fixed-size vs. autoextend). If you notice the undo tablespace growing uncontrollably, adjusting the UNDO_RETENTION temporarily can help reclaim space. Regular monitoring and adjustments are key to ensuring optimal database performance.