AWR retention

The following sql statement will give the current value for the AWR interval and retention period (in minutes)

select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval minutes",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval minutes"
from dba_hist_wr_control;

This means 21160 minutes /60/24 = 14 or 14 days.

In order to set the snapshot & retention interval the following procedure can be used.

execute dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 86400 )

This statement will set the snapshot interval to 3à minutes and the retention period to 60 days.

Pay attention that the DBA_HIST% tables are part of awr and that AWR requires a separate license.