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: The restored data block generated by UR UNDO_RETENTIONUPS in seconds

To determine the size of the UNDO tablespace required by Oracle, the following information is required: The restored data block generated by UR UNDO_RETENTIONUPS in seconds

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

UR UNDO_RETENTION in seconds
Number of restored data blocks generated by UPS 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"
2 FROM (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.

,