Posted on

How to Change AWR Retention Period in Oracle Database

You can first check the AWR Retention Period in Oracle with this

set lines 200
col SNAP_INTERVAL format a20
col RETENTION format a20
col SRC_DBNAME format a20

select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------
3847XX0535 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 3847XX0535

You can see that the interval is every hour and the retention is just 8 days.

You can change the retention to 30 days with this (30*24*60)

execute dbms_workload_repository.modify_snapshot_settings(retention => 43200);

If you also want to change the SNAP_INTERVAL, you can change it like that for taking snaps every 30 mins

execute dbms_workload_repository.modify_snapshot_settings(interval => 30);