View or Edit AWR Interval and Retention Settings

Script used to Check and Change AWR interval

— Check AWR interval snapshot_interval and retention_interval are in Minutes
select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
from dba_hist_wr_control;

— Change awr settings retention and interval are in Minutes
exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60);

Check what is using up SYSAUX tablespace:

set lines 200 pages 200
col schema_name for a30
select occupant_desc,space_usage_kbytes/1024 size_in_mb, schema_name Schema from v$sysaux_occupants order by 2 desc;

Clean up/Purge Stats;


If you need more help with database issues, feel free to send me an email ( and I'll gladly help.

About Jimbart