SYS.WRI$_ADV_OBJECTS对象清理-12Cand19C

有时候我们的sysaux表空间增长很快，通过查询我们发现是SYS.WRI$_ADV_OBJECTS对应太大导致的，那么这个时候我们就需要清理该表

以下步骤均在生产环境实际执行过

SQL>  COL SEGMENT_NAME FORMAT A30
SQL>  COL OWNER FORMAT A10
SQL>  COL TABLESPACE_NAME FORMAT A10
SQL>  COL SEGMENT_TYPE FORMAT A15
SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024
 2  "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE
 3  TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;
 
SEGMENT_NAME                   OWNER      Name         SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS               SYS        SYSAUX          15675 TABLE
WRI$_ADV_OBJECTS_IDX_01        SYS        SYSAUX           8764 INDEX
WRI$_ADV_OBJECTS_PK            SYS        SYSAUX           5959 INDEX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS        SYSAUX            488 INDEX
I_WRI$_OPTSTAT_H_ST            SYS        SYSAUX            249 INDEX
SYS_LOB0000007350C00005$$      SYS        SYSAUX       133.1875 LOBSEGMENT
SYS_LOB0000010641C00038$$      SYS        SYSAUX       110.1875 LOBSEGMENT
WRH$_SQL_PLAN                  SYS        SYSAUX             64 TABLE
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  SYS        SYSAUX             51 INDEX
SYS_LOB0000067470C00006$$      MDSYS      SYSAUX        50.1875 LOBSEGMENT

12C清理方式

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
EXEC DBMS_STATS.INIT_PACKAGE();
 
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

19c清理方式

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
EXEC DBMS_STATS.INIT_PACKAGE();

SQL> alter session set container=<PDB_NAME>;
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER TABLE WRI$_ADV_OBJECTS MOVE');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD'); /* This index is available from 19c only */

禁用advisor

DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/