Determining the Optimal Oracle Undo Retention Period

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