Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200;

select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc;

COL OCCUPANT_NAME FORMAT A30

SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;

COL SEGMENT_NAME FORMAT A30

COL OWNER FORMAT A10

COL TABLESPACE_NAME FORMAT A10

COL SEGMENT_TYPE FORMAT A15

SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;

1.  automatic clean-up default 31 days

          SELECT dbms_stats.get_stats_history_availability from Dual;

2. modify retention policy

       exec  dbms_stats.alter_stats_history_retention (14);

3. a manual cleaning

 exec dbms_stats.purge_stats (sysdate-14);

 

ALTER TABLE WRI$_ADV_OBJECTS MOVE;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;