Reduce Size of SYSAUX

oracle

I am trying to reduce the size of our SYSAUX Table. Currently the largest four occupants are below. As you can see it is mostly the Server Manageability items.

select occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
  where space_usage_kbytes > 0 order by space_usage_kbytes desc
/

OCCUPANT_DESC                                       SPACE_USAGE_KBYTES     
Server Manageability - Advisor Framework                4613440    
Server Manageability - Optimizer Statistics History     2522944    
Server Manageability - Automatic Workload Repository    1457280    
Enterprise Manager Repository                       172480

I have reduced retention to 7 days

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                          7

I have purged statistics older than 7 days:

exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
PL/SQL procedure successfully completed.

All this but no luck reducing space in the tablespace. I also tried following the instructions in Purging statistics from the SYSAUX tablespace but the space is still full.

Any suggestion?

Best Answer

Try moving some objects into another tablespace. Documentation on this is quite confusing, referring to an uncertain pl/sql procedure, that should be used. Though, there is no mention what procedure it is. I would use alter table ... move ...

Edit: (thanks to @Jack Douglas): Instead of alter, there is a move_procedure defined in V$SYSAUX_OCCUPANTS that should be used to move those occupants.

Related Question