Recommended UNDO Tablespace Size in Oracle 19c Using the Undo Advisor (SQL Only)

When you change UNDO_RETENTION in Oracle Database 19c, you must ensure that your UNDO tablespace is large enough to support the new retention period — especially if you enable RETENTION GUARANTEE.

Check this link: Understanding UNDO Configuration and RETENTION GUARANTEE in Oracle 19c

Oracle provides a built-in Undo Advisor that calculates the required UNDO size based on real workload statistics.

This article explains how to calculate the recommended UNDO size using SQL only.

1. Step 1 – Check Current UNDO Retention

SHOW PARAMETER undo_retention;

Example:

undo_retention = 900

This means Oracle tries to retain undo for (900 seconds) 15 minutes.

2. Step 2 – Understand How Oracle Calculates UNDO Size

Oracle uses data from:

V$UNDOSTAT

This view stores undo generation statistics every 10 minutes.

Key columns:

• UNDOBLKS – undo blocks generated
• TXNCOUNT – number of transactions
• MAXQUERYLEN – longest query duration
• UNXPSTEALCNT – times unexpired undo was stolen

3. Step 3 – Use Oracle Undo Advisor

Oracle’s official sizing formula:

Undo Size = (Undo Blocks Per Second × Retention Seconds × Block Size)

– check the undo retention (Seconds)

SELECT value FROM v$parameter WHERE name = 'undo_retention';

Default value: 900 Seconds

– Get Block Size

SELECT block_size
FROM dba_tablespaces
WHERE tablespace_name =
    (SELECT value FROM v$parameter WHERE name='undo_tablespace');

Typical block size = 8192 bytes (8KB)

The simplest SQL-only approach is this:

SELECT ROUND(
       (
         (SELECT MAX(undoblks) FROM v$undostat)
         *
         (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'undo_retention')
         *
         (SELECT block_size 
            FROM dba_tablespaces 
           WHERE tablespace_name = 
                 (SELECT value 
                    FROM v$parameter 
                   WHERE name = 'undo_tablespace')
         )
       ) / 1024 / 1024
) AS Recommended_undo_size_mb
FROM dual;

But this is approximate.

If using RETENTION GUARANTEE → add 20–30% safety margin.

4. Final Recommendation

When increasing UNDO_RETENTION:

1. Calculate undo generation rate
2. Apply official formula
3. Add safety margin
4. Resize UNDO tablespace
5. Monitor V$UNDOSTAT
6. Enable RETENTION GUARANTEE only if necessary