Oracle Database Technology

--contributed by Manjunath Ulthi, It becomes a challenge to figure out the right amount of UNDO to be allocated to a database. Try this method : UndoSpace = [UR * (UPS * DBS)] + (DBS * 24) where, (UR) UNDO_RETENTION in seconds - an init parameter (UPS) Number of undo data blocks generated per second (DBS) Overhead varies based on extent and file size (db_block_size) Two (UPS) can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula (DBS) requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds: SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat; The following query calculates the number of bytes needed: SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter