Home / Oracle Database Material / AWR Snapshot Retention Period

AWR Snapshot Retention Period

AWR Snapshot Retention Period Configuration


This article explores the process of modifying the AWR snapshot retention period in Oracle.

Regarding AWR Reports

The Oracle MMON background process gathers AWR statistics and creates snapshots according to the specified retention period, which may exceed the threshold metric. Enabling this task involves adjusting the STATISTICS_LEVEL parameter value to TYPICAL, facilitating automatic snapshot creation. Conversely, disabling it requires setting the STATISTICS_LEVEL parameter value to BASIC, ceasing automatic snapshot generation.


SQL> show parameter statistics_level

———————————— ———– ——————————
client_statistics_level string TYPICAL
statistics_level string TYPICAL

SQL> show parameter control_management_pack_access

———————————— ———– ——————————
control_management_pack_access string DIAGNOSTIC+TUNING


Modifying Snapshot Settings in AWR

To adjust the automatic AWR snapshot collection, we utilize the modify_snapshot_settings function to alter the snap_interval and retention period values.

Default Snapshot Settings:

By default, the interval and retention values are set to 60 minutes and 8 days, respectively.


SQL> col snap_interval for a25

SQL> col retention for a25

SQL> select snap_interval, retention from dba_hist_wr_control;

———————————- ———————————————–
+00000 01:00:00.0 +00008 00:00:00.0


Modify the Snapshot Setting:

Change the snap_interval value to 30 min and retention value to 30 days(60*24*30).

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);

PL/SQL procedure successfully completed.


Check and verify the new Snapshot Settings:


SQL> select snap_interval, retention from dba_hist_wr_control; 

———————————— —————————————
+00000 00:30:00.0 +00030 00:00:00.0



  • 1

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also


Manually remove ODA DB HOME

Manually remove OLD DB HOMES if odacli-delete dbhome fails Please follow the instructions below: 1. …

Leave a Reply