Monday , September 25 2023

How To Change AWR Retention and Interval Values

If Tuning and Diagnostic Packs is installed, automatic snapshots can be created with AWR and we can get information about the database performance among the related snapshots. Tuning and Diagnostic Packs require a separate license from the database.

What is interval and retention?

interval means to the interval of automatic snapshot, retention means to how long the snapshot information is stored.

– retention: If set to 0, it is stored indefinitely. In minutes. The required value for 30 days will be 30 * 24 * 60 = 64800.
– interval: If set to 0, AWR is turned off. It takes the value in minutes.

You can find the current values ​​with the following query.

Change retention and interval values

You can change the existing values ​​with the dbms_workload_repository.modify_snapshot_settings procedure as follows.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *