Oracle Database Discussions

Oracle Database 10.1

We have a SYSAUX tablespace that has grown to nearly 15GB - approx 14.5GB of this is being used by SM/Advisor. Originally the underlying WRI$_ADV_OBJECTS table had 78+ million rows, but I have managed to reduce this to 2500 rows using DBMS_ADVISOR.DELETE_TASK to delete rows consumed for a segment advisor task. I have set the relevant snapshot + retention settings to a minumum and also taken off AUTO_EXTEND on the tablespace. The problem I have is that SYSAUX is still retaining the 14.5GB used by SM/Advisor.

How can I reduce SYSAUX back down to 1-2GB and release this space?

The SYSAUX Occupants view shows that there is no Move Procedure for SM/Advisor objects - does this mean that the underlying table and index (WRI$_ADV_OBJECTS + WRI$_ADV_OBJECTS_PK) cannot be moved into a different tablespace, or can they just be moved using "Alter Table" like any other table/index in a normal tablespace? Would this reduce the size of the SYSAUX tablespace anyway if I moved the objects back into SYSAUX afterwards?


SQL> select occupant_name, space_usage_kbytes, move_procedure_desc
2 from v$sysaux_occupants where occupant_name like 'SM%';

OCCUPANT_NAME SPACE_USAGE_KBYTES MOVE_PROCEDURE_DESC
---------------------------------------------------------------- ------------------ ----------------
SM/AWR 43648 *** MOVE PROCEDURE NOT APPLICABLE ***
SM/ADVISOR 14431360 *** MOVE PROCEDURE NOT APPLICABLE ***
SM/OPTSTAT 72576 *** MOVE PROCEDURE NOT APPLICABLE ***
SM/OTHER 7872 *** MOVE PROCEDURE NOT APPLICABLE ***


If anyone has any recommendations on how reduce SYSAUX back down to its original size I would be grateful for the advice.

Thanks.