Oracle undo tablespace Size Estimation

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

In the above formula, 1st and 3 parameters can be easily obtained from the parameter file:
SQL> show parameter undo_retention

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_retention integer 5400
SQL> show parameter db_block_size

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_block_size integer 8192

Now it is important to determine the 2nd parameters: the total number of undo blocks generated per second, which can be obtained from V $ UNDOSTAT.

UNDO volume generated per second:
SQL> SELECT (SUM (undoblks)/SUM (end_time-begin_time) * 86400) FROM v $ undostat;

(SUM (UNDOBLKS)/SUM (END_TIME-BEGIN_TIME) * 86400)
--------------------------------------------------------------
117.97590055

Normal estimate value:
SELECT (UR * (UPS * DBS) + (DBS * 24) AS "Bytes"
FROM (SELECT value as ur from v $ parameter WHERE name = 'undo _ retention '),
(SELECT (SUM (undoblks)/SUM (end_time-begin_time) * 86400) as ups from v $ undostat ),
(Select block_size as DBS from dba_tablespaces where tablespace_name =
(Select upper (value) from v $ parameter where name = 'undo _ tablespace '));

Maximum estimation:
SELECT (UR * UPS * DBS + DBS * 24)/1024/1024 AS "undo size (M )"
FROM (SELECT value as ur from v $ parameter WHERE name = 'undo _ retention '),
(SELECT max (undoblks/(end_time-begin_time) * 24*3600) as ups from v $ undostat ),
(SELECT value as dbs from v $ parameter WHERE name = 'db _ block_size ');

We recommend that you make statistics on the day when the business volume is heavy, and set the UNDO tablespace size to the maximum estimate value or above.

Note: This article describes How To Size UNDO Tablespace For Automatic IC Undo Management [ID 262066.1].