How to Find Database Optimal and Needed Undo Size

Since oracle 9i, rollback segments are re-named undo logs, traditionally it were stored in rollback segments until a commit or rollback segments were issued.
Automatic undo management allows the DBA how long information should be retained after commit. The larger your undo tablespace the more you can hold for long running DML operation (Preventing "snapshot to old error on long running queries).
You can choose to allocate a specific size for the UNDO tablespace and then set the optimal UNDO_RETENTION according to UNDO size. This is specially useful when you disk space is limited and you do not want to allocate more space than required UNDO size.

OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE *       UNDO_BLOCK_PER_SEC)

Find Actual Undo Size:
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#;

UNDO_SIZE
-----------
  7948206080

Find Undo Blocks per Second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
        6.47
Find Undo Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
--------------------
                8192

OPTIMAL UNDO RETENTION = 7948206080 /(8192 * 6.47)
149959.8145 [sec]
Using below query you can find all those information collectively:

Script: To Find calculated Optimal Undo retention:
Using below query you can find all those above information collectively.
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';

ACTUAL UNDO SIZE [MByte]   UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
----------------------    ---------------- -----------------------------
7580 10800 149960 
Script: Planning to Calculate Needed Undo size for database:
In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, thus the setting for these are determined by the level of DML activity in the database:
1. If you are using heavy DML operation make sure there is enough sized rollback segments.
2. If you expect heavy DML load then must have multiple undo tablespace.
3. Try to limit the number of simultaneous users per UNDO to four.
4. For large batch transactions create special large extent rollback segments in a separate tablespace from the other rollback segments,
only bring them online when needed and use SET TRANSACTION command to assign them to specific transactions.
5. Try to avoid running large batch transactions simultaneously with OLTP or smaller transactions.
UNDO_SIZE = UNDO_RETENTION * DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC

  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';

ACTUAL UNDO SIZE [MByte]    UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
-----------------------     ----------------         -----------------------
7580 10800 545.90