1.查看sysaux表空间对象具体的占用空间http://www.cndba.cn/hbhe0316/article/116510

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;

SQL> select segment_name,sum(bytes)/1024/1024 from dba_segments where tablespace_name='SYSAUX' group by segment_name order by 2 desc

2.具体为WRI$_ADV_OBJECTS表占用的较多

3.清理方式
12C清理方式http://www.cndba.cn/hbhe0316/article/116510

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清理方式http://www.cndba.cn/hbhe0316/article/116510http://www.cndba.cn/hbhe0316/article/116510

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;/

参考文档：SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)http://www.cndba.cn/hbhe0316/article/116510

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle