Working with Undo Tablespace and Undo Retention

(1)There formula for calculating optimal UNDO_RETENTION is this:
OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE / ( DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC )


-->Actual undo tablespace size can be seen from:-

select bytes from dba_data_files where tablespace_name like '%UNDO%';


-->Actual DB_BLOCK_SIZE can be seen from pfile or:-

select name,value from v$parameter where name like '%block%';


-->For UPS(undo per second) here goes another query which you need to fire another query, that is:-

Select sum(undoblks) / sum((end_time-begin_time)*86400)
from v$undostat;


Here is the single query which does it all:-

This indicates you the actual undo retention value required by the DB in bytes.


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,v$tablespace b,dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,v$parameter e,v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec FROM v$undostat)g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



OUTPUT=

output will be something like this:-

ACTUAL UNDO SIZE [MByte]------------------------17000
UNDO RETENTION [Sec] ------------------------18000
OPTIMAL UNDO RETENTION [Sec]--------------------3338


(2) Now to calculate the actual size of the undo tablespace you need to do the following calculation:-

UR=undo retention
UPS=undo per second
DBS=data block size

undo tablespace = (UR *(UPS * DBS)) + (DBS * 24)

UR and DBS can be obtained from the parameter file or by the queries mentioned above,
for UPS here goes another query which you need to fire another query, that is:-
Select sum(undoblks) / sum((end_time-begin_time)*86400)
from v$undostat;