To determine the optimal value for the UNDO_RETENTION parameter, you must first calculate the actual amount of undo that the database is generating. Once you know approximately how much undo the database is generating, you can calculate a more precise value for the UNDO_RETENTIONparameter.
Use the following formula to calculate the optimal value of the UNDO_RETENTION parameter:
OPTIMAL UNDO_RETENTION = UNDO SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)
Calculate the current space allocated for undo
SQL> select sum(d.bytes) "undo" 2 from v$datafile d, 3 v$tablespace t, 4 dba_tablespaces s 5 where s.contents = 'UNDO' 6 and s.status = 'ONLINE' 7 and t.name = s.tablespace_name 8 and d.ts# = t.ts#; undo ---------- 68232937472
Calculate the value of UNDO_BLOCKS_PER_SEC
select max(undoblks/((end_time-begin_time)*3600*24)) 2 "UNDO_BLOCK_PER_SEC" 3 FROM v$undostat; UNDO_BLOCK_PER_SEC ------------------ 359.366667
Check the block size for your database:
SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
Calculate the optimal value for the UNDO_RETENTION parameter using the formula, for example, giving a result in seconds: 23,158 = 68232937472/(359.366667 * 8,192).
In this case, assigning a value of 1,800 seconds for the undo_retentionparameter is appropriate.
If disk space isn’t an issue for you, you can instead choose the ideal value for the UNDO_RETENTION parameter for
your instance, The formula now becomes:
size of undo = undo_retention * db_block_size :undo_block_per_sec
SQL> select d.undo_size/(1024*1024) "Current UNDO SIZE", 2 SUBSTR(e.value,1,25) "UNDO RETENTION", 3 (to_number(e.value) * to_number(f.value) * 4 g.undo_block_per_sec) / (1024*1024) 5 "Necessary UNDO SIZE" 6 from ( 7 select sum(a.bytes) undo_size 8 from v$datafile a, 9 v$tablespace b, 10 dba_tablespaces c 11 where c.contents = 'UNDO' 12 and c.status = 'ONLINE' 13 and b.name = c.tablespace_name 14 and a.ts# = b.ts# 15 ) d, 16 v$parameter e, 17 v$parameter f, 18 ( 19 Select max(undoblks/((end_time-begin_time)*3600*24)) 20 undo_block_per_sec 21 from v$undostat 22 ) g 23 where e.name = 'undo_retention' 24 and f.name = 'db_block_size'; Current UNDO SIZE UNDO RETENTION Necessary UNDO SIZE --------------------- --------------------- ------------------------- 65072 86400 242572.5
The query shows that you should set the size of the undo tablespace in this database to around 242572.5 MB.
Set the undo retention to 86400 minutes:
alter system set undo_retention=86400 scope=both;
Refrence : https://www.apress.com/gp/book/9781430261872