Undo tablespace Size evaluation

Reference for Undo tablespace Size evaluation:

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

A. Ur: undo_retention in seconds

SQL> show parameter undo_retention;

B. Ups: calculates the number of Undo data blocks generated per second during business peaks.

SQL> select max (undoblks/(end_time-begin_time) * 24*3600) from V $ undostat;

C. DBS: obtain the data block size.

SQL> show parameter db_block_size;

 

Reference formula:

Undospace = Ur * ups * DBS + DBS * 24

Refer to the statement for calculating the Undo tablespace size:

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 ');

 

If the size of the Undo tablespace is smaller than the calculated size, we recommend that you increase it to a value greater than the calculated recommended value.