Estimate undo tablespace size

The V$UNDOSTAT view displays statistical data to show how well a database is performing. Each row in the view represents statistics collected for a 10-minute interval. The following query gives the number of undo blocks generated per second. This value needs to be multiplied by the size of an undo block – the same size as the database block defined by the DB_BLOCK_SIZE parameter.


The END_TIME and BEGIN_TIME columns are DATE datatypes.

-- UNDO per second
SELECT (SUM(undoblks))/SUM((end_time-begin_time)*86400) FROM v$undostat;

Value of DATA BLOCK SIZE
SELECT value FROM v$parameter WHERE lower(name) = 'db_block_size';

-- Value for UNDO RETENTION
SELECT value FROM v$parameter WHERE lower(name) = 'undo_retention';

Multiplying the value from the three queries above gives the UNDO tablespace required.

Thanks MohanNair,

Again i have a questions here.

1)Why 86400 value is multiplying to Sum(end_time-begin_time).

Suppose : i have only one record in v$undostat means 10mints and undo generate blocks (undoblk) are 20000. For getting UNDO per second is

10 M= 20000
1M=20000/10
1Sec = 20000/10*60 . Am i rite, please correct me here.

2) Is the Db_block_size equal to Overhead.
  Could you please explain me below statement.
  (The Overhead should account for metadata such as bitmaps. )

3) This is formual/calc is for all ready existing database. Suppose i want to calucate UNDO tablespace (estimated) space  for new database(OLTP). What are the things i need to consider.

Thanks a lot for your reply.

Thanks & Regards,
Sreeni