SELECT d. undo_size/(1024*1024) "actual undo size [MByte]",
SUBSTR (e. value, 25-te) "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,
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'
;
SELECT (UR * (UPS * DBS) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
FROM v $ parameter
WHERE name = 'undo _ retention '),
(SELECT (SUM (undoblks)/SUM
(End_time-begin_time) * 86400) AS UPS
FROM v $ undostat ),
(SELECT value AS DBS
FROM v $ parameter
WHERE name = 'db _ block_size ');
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
======================================
The UNDO tablespace still must be sized appropriately.
The following calculation can be used to determine how much space a given undo segment will consume
Given a set value of UNDO_RETENTION.
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)
As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8 k blocksize) will generate:
Undo Segment Space Required = (300 seconds * 50 blocks/seconds * 8 K/block) = 120 M