What size of UNDO tablespace do you need?

Measure the Size of UNDO tablespace you need

Following information is needed to measure the size of UNDO tblspce:

- UR (Undo_retention) in sec

- UPS ( Undo datablocks Per Second)

- DBS (Data_block_size)

UNDOSPACE = (UR * (UPS * DBS)) + (DBS * 24)

UNDO_RETENTION and DB_BLOCK_SIZE can be retrieved from PARAMETER.

UPS is gotten from V$UNDOSTAT:

SELECT (SUM(undoblks) / SUM((end_time – begin_time) * 86400)) UPS FROM v$undostat;

86400 sec.= 24 hrs * 60min * 60 sec

QUERY TO OBTAIN THE SIZE FOR YOUR UNDO TABLESPACE

SELECT (UR * (UPS * DBS)) + (DBS * 24) “BYTES NEEDED”

FROM (SELECT value AS UR

FROM v$parameter

WHERE name = ‘undo_retention’),

(SELECT (SUM(undoblks) / SUM ((end_time – begin_time) * 86400)) UPS

FROM v$undostat),

(SELECT value DBS

FROM v$parameter

WHERE name = ‘db_block_size’);

If you want to turn it to Megabytes, you should devide it by 1,048,576 bytes.