Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Mar 29 '12 at 13:36
1  
reducing the size of objects in a tablespace will not reduce the size of the tablespace automatically - see here –  Jack Douglas Mar 30 '12 at 9:26

1 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.

share|improve this answer
2  
From SYSAUX? I'd advise against it. –  Phil Mar 30 '12 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. –  Jack Douglas Mar 30 '12 at 9:22
    
I also found this, but did not read it as carfeully, as I should :-) Now I see it :-) –  ipip Mar 30 '12 at 9:50
    
+1 good edit :-) –  Jack Douglas Mar 30 '12 at 15:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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