We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function.
The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days .
DEFAULT SETTING:
select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION --------------------------------------------------------------------------- --------------------------------------------------------------------------- +00000 01:00:00.0 +00008 00:00:00.0
Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30)
The values for both ‘interval’ and ‘retention’ are expressed in minutes .
execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);
Verify the new setting:
SQL> select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION --------------------------------------------------------------------------- ----------------------------------------- +00000 00:30:00.0 +00030 00:00:00.0
Thank you so much for posting this topic,it greatly helps me