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;

I have purged statistics older than 7 days:

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?

  • 1
    You don't mention your version but if you are using 11g Automatic SQL Plan Management you may be hitting a bug that allows its entried in SYSAUX to grow... a lot... details here juliandontcheff.wordpress.com/2011/06/13/…
    – David Mann
    Commented Mar 29, 2012 at 13:36
  • 1
    reducing the size of objects in a tablespace will not reduce the size of the tablespace automatically - see here Commented Mar 30, 2012 at 9:26

2 Answers 2


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.

  • 2
    From SYSAUX? I'd advise against it.
    – Philᵀᴹ
    Commented Mar 30, 2012 at 8:05
  • 3
    -1 this is a very bad idea, don't do it. See here for how to move Occupants out of the SYSAUX tablespace. Commented Mar 30, 2012 at 9:22
  • I also found this, but did not read it as carfeully, as I should :-) Now I see it :-)
    – Iľja
    Commented Mar 30, 2012 at 9:50

If you run this query and see lines with "ALL" then the link below could be causing your issue:

 FROM v$statistics_level

Look at the following blog post for the cause and solution to the issue:
Why is my SYSAUX Tablespace so Big? Statistics_level=ALL

  • 1
    Please include the most important points from that post. It may disappear, move to somewhere else, be stolen by aliens and so on - rendering your answer not that useful. Commented Dec 9, 2015 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.