1. UNDO usage on the database might hit the water mark making the job to administer it tedious.
Monitor who is using UNDO:

select
s.sid,s.serial#, osuser,terminal,program,sql_id,prev_sql_id,module,
s.username,
r.name “RBS name”,
t.start_time,
t.used_ublk “Undo blocks”,
t.used_urec “Undo recs”
from
v$session s,
v$transaction t,
v$rollname r
where
t.addr = s.taddr and
r.usn = t.xidusn;

2. Script to estimate the amount of UNDO tablespace size required for the database

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
“NEEDED UNDO SIZE [MByte]”
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’;

3. Estimate the amount of time required for a transaction to use UNDO:

select usn, state, undoblockstotal “Total”, undoblocksdone “Done”,
undoblockstotal-undoblocksdone “ToDo”,
decode(cputime,0,’unknown’,to_char(sysdate+(((undoblockstotal-undoblocksdone) /
(undoblocksdone / cputime)) / 86400),’dd-mon-yy hh24:mi’)) “Estimated time to complete”
from v$fast_start_transactions;