How to calculate the recommended UNDO tablespace size in Oracle

Formula: NEEDED = UNDO_RETENTION (sec) * peak_undo_blocks_per_sec * db_block_size. Compares the number against the actual UNDO tablespace size so you can see whether you have headroom or need to grow.

SELECT d.undo_size / 1024 / 1024 AS "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value, 1, 25)      AS "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / 1024 / 1024
         AS "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM(a.bytes) AS undo_size
      FROM   gv$datafile     a,
             gv$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,
     gv$parameter e,
     gv$parameter f,
     (SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) AS undo_block_per_sec
      FROM   v$undostat) g
WHERE  e.name = 'undo_retention'
AND    f.name = 'db_block_size';