Planning UNDO RETENTION ...

Find UNDO Size:-

select sum(d.bytes) "undo"
from v$datafile d,
v$tablespace t,
dba_tablespaces s
where s.contents = 'UNDO'
and s.status = 'ONLINE'
and t.name = s.tablespace_name
and d.ts# = t.ts#;

 
Then DB_BLOCK_SIZE :-

show parameter db_block_size -- (Convert value to bytes, i.e. 8 Kb = 8192 bytes)


Finally figure out UNDO_BLOCKS_PER_SEC :-

select max(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;


Formula to calculate UNDO RENTENTION value

UNDO_RETENTION = UNDO SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)