Sizing Undo Tablespace

Sizing Undo Tablespace

Sizing an Undo Tablespace requires 3 pieces of data.

(UR) UNDO_RETENTION in seconds. (init parameter)
(UPS) Number of undo data blocks generated per second (query v$undostat)
(DBS) Overhead varies based on extent and file size (init parameter db_block_size)

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

To Calculate UPS,

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

The following query calculates the size of undo tablespace (Mega bytes) needed:

SQL> SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 "Mega Bytes"
FROM (SELECT value UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) UPS FROM v$undostat),
(select block_size DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

Thanks