How to calculate UNDO table space optimal size in oracle database?

Here is the list of steps to follow to get optimal UNDO table space.

Undo Space = UNDO_RETENTION in seconds * undo blocks for each second * block_size of the database/TBS

lets say if undo retention 1 hr 60*60 seconds
undo block usage per second 300
undo block size 8k 8192

so optimal undo size=60*60 *300*8192/1024/124=843 MB



To check the block_size on database


SELECT TO_NUMBER(value) as DB_BLOCK_SIZE   FROM v$parameter WHERE name = 'db_block_size';

find the number of undo block per second;

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) as UNDO_BLOCK_PER_SECOND   FROM v$undostat;

To check the undo retention of tablespaces.

select name,value FROM v$parameter WHERE name='undo_retention';