Reduce Size of SYSAUX

Question

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?

Answer 1

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.