Blog

In our other article, we have already discussed how to generate AWR Report in Oracle database. In this article today, we will learn about how to check retention period of AWR in oracle.

Issue the below command to check the retention period of AWR Report :

set lines 200
set pages 400
col SNAP_INTERVAL format a20
col RETENTION format a25
col SRC_DBNAME format a20
 
select * from dba_hist_wr_control;

So, the snapshot interval is 15 mins, and retention period of AWR is 30 days(43200/60/24).

Modify the retention period of AWR Report :

Suppose we want to modofy the snaphot interval from 15 to 30 mins and the retention period from 30 to 40 days.

Then issue the command as below :

execute dbms_workload_repository.modify_snapshot_settings(interval => 30);

execute dbms_workload_repository.modify_snapshot_settings(retention => 57600);

So, here you can modify the values and change accordingly. These are the steps to check retention period of AWR in Oracle. Hope this helps!!