Tag: retention

Posted on

How to Change AWR Retention Period in Oracle Database

water drop photo

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

1
2
3
4
5
6
7
8
9
10
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)

1
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

1
execute dbms_workload_repository.modify_snapshot_settings(interval => 30);