How to estimate the size of the UNDO tablespace required by the Oracle database

To determine the size of the UNDO tablespace required by Oracle, the following information is required:

UR    UNDO_RETENTION in seconds
UPS  Number of restored data blocks generated per second
DBS   Db_block_size

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

UNDO_RETENTION is a parameter that controls the restored data volume that is retained to provide read consistency. It is defined in seconds and can be set in the initialization file or dynamically modified using the alter system command.

SQL & gt; ALTER SYSTEM SET UNDO_RETENTION = 900;

SQL> show parameter undo_retention

NAME                               TYPE      VALUE
-----------------------------------------------------------------------------
Undo_retention                     Integer   900

If the value is 900, the restored data can be retained for 15 minutes. Of course, enough storage space is required.

Then, how to calculate the number of restored data blocks generated per second can be queried through the values of the begin_time, end_time, and undoblks fields in the v $ undostat view. The calculated SQL statement is as follows:

SQL> SELECT (UR * (UPS * DBS) + (DBS * 24) AS "Bytes"
 2FROM     (SELECT value AS UR
 3           FROM v $ parameter
 4           WHERE name = 'undo _ retention '),
 5   (SELECT (SUM (undoblks)/SUM (end_time-begin_time) * 86400) AS UPS
 6   FROM v $ undostat ),
 7   (SELECT value AS DBS
 8   FROM v $ parameter
 9   WHERE name = 'db _ block_size ');

    Bytes
----------
445814.844

In order to obtain results that meet business needs, the calculation should generally be performed when the database load is the heaviest in a day.