Lutz Haake | Freelance Oracle Consultant

The AWR had to be rebuilt, and at the same time new values for the retention time were set. How big will the SYSAUX table­space grow? After some days of typical activity, I used this SQL to project the current AWR SYSAUX space usage into the future:

select q1.awr_days, q3.awr_usage_mb, q2.retention_days, awr_usage_mb * retention_days/ awr_days awr_usage_projection_mb from
(
select extract ( DAY from (max(begin_interval_time) - min(begin_interval_time)) ) +1 awr_days, count(*) awr_snaps from dba_hist_snapshot
) q1
, (
select extract(DAY from retention) retention_days from  dba_hist_wr_control
) q2
, (
select SPACE_USAGE_KBYTES/1024 awr_usage_mb from v$sysaux_occupants where OCCUPANT_NAME = 'SM/AWR'
) q3
;