Modifying AWR Automatic Snapshot Settings

Verify Current setting:

SQL> set linesize 100
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
2684842560 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT


Change the interval to 10 minutes and retention to 10 days:

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 10,retention => 14400);

Verify change:


SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
2684842560 +00000 00:10:00.0    +00010 00:00:00.0    DEFAULT