The default frequency (automatic) of generation of Oracle Automatic Workload Repository (AWR) is every 1 hour (60 minutes). Sometimes, an Oracle DBA might have set this to generate more frequently, say, every 15 minutes i.e 4 times an hour to troubleshoot a performance issue and later would have forgotten to set it back to 1 hour frequency.
This will fill up the SYSAUX tablespace very quickly. Please follow the below list of instructions to reset it back to 1 hour frequency.
This is tested in an Oracle 19c database.
select snap_interval, retention from dba_hist_wr_control;
If it is run every 15 minutes, then the output will show +00000 00:15:00.0
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);
The AWR snapshots will now be generated every 1 hour instead of every 15 minutes.
select snap_interval, retention from dba_hist_wr_control;
The output should now show +00000 01:00:00.0
