The DBMS_UNDO_ADV Package provides some excellent feedbackup
to help the best value for the undo_retention
Sql> select dbms_unod_adv longest_query(sysdate-1/24,sysdate)
as best_undo_time from dual;
This gives longest query of the instance took around 845
seconds is good for starting point of undo retention.
The below query will be appropriate value for undo_retention
Sql> select dbms_undo_adv.required_retention(sysdate-30,sysdate)
as reqd_retn from dual;
Its give last 30 days longest _query procedure
SQL> SELECT dbms_undo_adv.longest_query LONGEST_QUERY
FROM dual;
LONGEST_QUERY
-------------
7228
SQL> show parameter undo ;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
undo_management string
AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1/24,
SYSDATE) LONGEST_QUERY FROM dual;
LONGEST_QUERY
-------------
1536
SQL> select
dbms_undo_adv.required_retention(sysdate-30,sysdate) as reqd_retn from dual;
REQD_RETN
----------
7228
