How to modify Statistics History Retention

Check current retention value

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

Modify rention value

SQL> exec dbms_stats.alter_stats_history_retention(retention=>45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         45

SQL> select dbms_stats.get_stats_history_Availability from dual;

GET_STATS_HISTORY_AVAILABILITY

---------------------------------------------------------------------------

04-AUG-17 06.26.01.162917000 PM +05:30

SQL> select * from dba_tab_stats_history where owner='TROY';

OWNER           TABLE_NAME                PARTITION_NAME            SUBPARTITION_NAME         STATS_UPDATE_TIME

--------------- ------------------------- ------------------------- ------------------------- -------------------------------------------

TROY            MY_SOURCE                                                                     02-SEP-17 04.55.13.597339 PM +05:30

TROY            MY_SOURCE                                                                     02-SEP-17 05.00.38.153880 PM +05:30