Usage and Storage Management of SYSAUX [ID 329984.1]
====================================================

-- SYSAUX CONSUMPTION
select occupant_name, schema_name, space_usage_kbytes/1024 MB from v$sysaux_occupants order by 3 desc;

-- 15 days keep AWR stats (21600min), default is 7days
-- AWR repository it self should purge expired snapshots
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (21600, 30);

    -- list AWR snapshots
    SELECT snap_id, begin_interval_time, end_interval_time  FROM dba_hist_snapshot  ORDER BY 1;
    
    -- drop snapshots
    EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1, high_snap_id=>20697);
    
    -- AWR info consumption
    @?/rdbms/admin/awrinfo.sql
    
    -- AWR recreation
    connect / as sysdba 
    @?/rdbms/admin/catnoawr.sql 
    @?/rdbms/admin/catawrtb.sql

-- SM/OPTSTAT
select dbms_stats.get_stats_history_retention from dual;
exec dbms_stats.alter_stats_history_retention(10);
exec DBMS_STATS.PURGE_STATS(to_timestamp_tz('01-02-2010 00:00:00 Europe/Berlin','DD-MM-YYYY HH24:MI:SS TZR'));