由 dawei ?
1. 通过SQL语句查看 SYSAUX 占用情况，和表空间里对象占用情况
SQL> SET LINES 120 SQL> COL OCCUPANT_NAME FORMAT A30 SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; ? OCCUPANT_NAME SPACE_USAGE_KBYTES ------------------------------ ------------------ SM/ADVISOR 5901376 SM/OPTSTAT 574080 ? 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 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=‘SYSAUX‘ ORDER BY BYTES DESC) WHERE ROWNUM<=10; SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE ------------------------------ ---------- ---------- ---------- --------------- WRI$_ADV_OBJECTS SYS SYSAUX 3600 TABLE WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX HEAT_MAP SYS SYSAUX 600 TABLE
?
A.哪果是表空间里是?heat_map占用比较大 ，可以查看HEAT_MAP 是否开启
? ?这个段为12c新特性提供的新功能：它存储在行和段级别系统生成的数据使用统计信息——这些信息可以用来自动化压缩和移动数据，以降低存储成本、提高性能和优化Oracle数据库存储，并且默认是启用的状态，但如果数据库host objects过多将会导致该段占用大量SYSAUX表空间，通过MOS搜索得到该问题的解决方案：
? ?
? ALTER SYSTEM SET “_drop_stat_segment” =1; ? ?数据库不用重启
? ? ? 通过调整数据库的隐藏参数来进行段的清除（
文档 ID 2024036.1）
?
B. SM/OPTSTAT ?占用空间排名 参考：文档 ID )??
? ? 这个是由于，存放的历史统计信息太多， 更统计信息频率，和数据量变更，以及保存时间都有关系。
? ? 1.?自动清理策略默认31天
? ? ? ? ? select dbms_stats.get_stats_history_availability from dual;
? ? 2. 修改保留策略
? ? ? ? ? ?exec dbms_stats.alter_stats_history_retention(1);
? ? 3. 手动清理一次
? ? ? ? ? ?exec dbms_stats.purge_stats(sysdate-10);
? ? 4. ?如果表还是比较大，可以考虑在线移动
?
a. 重创建表
CREATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY_B AS SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY ; TRUNCATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY ; INSERT INTO WRI$_OPTSTAT_HISTGRM_HISTORY SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY_B; COMMIT; drop table WRI$_OPTSTAT_HISTGRM_HISTORY_B purge;
?
? ? ??? b.通过表移动的方式
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move; 文档ID 454678.1
?
? ??5. 手动重创建索引 ?
alter index xxx rebuild online ;
?
?
C. SM/ADVISOR 占用空间排名 参考：文档 ID 2440139.1)??
? ? ? 查询视图 V$SYSAUX_OCCUPANTS 显示
SM/ADVISOR 占用空间排名靠前.
? ? ? 这个是由于12.2版本的一个新特性，即优化器统计信息顾问每天在维护窗口期间自动运行，因而引发了该问题。
? ? ? AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。
? ?
例如:
?
SQL> COL TASK_NAME FORMAT A35 SQL> SELECT TASK_NAME,COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC; ? TASK_NAME CNT ----------------------------------- ---------- AUTO_STATS_ADVISOR_TASK 27082431 SYS_AUTO_SPM_EVOLVE_TASK 19 SYS_AUTO_SQL_TUNING_TASK 39
?
?
?
解决办法：
?
1. 这个自动运行的任务(AUTO_STATS_ADVISOR_TASK) 可以被删除进而释放它占用的大量空间。
DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := ‘AUTO_STATS_ADVISOR_TASK‘; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; /
?
一旦任务被删除，所有依赖Auto Stats Advisor Task的相关历史输出信息将从WRI$_ADV_OBJECTS表中一并删除。
删除任务AUTO_STATS_ADVISOR_TASK可能引发如下报错：
ORA-20001: Statistics Advisor: Invalid Task Name For the current user
如果上述报错出现，可以用下面的方法重建AUTO_STATS_ADVISOR_TASK来解决：
SQL> connect / as sysdba SQL> EXEC DBMS_STATS.INIT_PACKAGE();
?
?
删除任务之后，重组表和所有索引：
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE; SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
?
2.如果表WRI$_ADV_OBJECTS中有太多和Auto Stats Advisor Task相关的记录，删除任务时可能会需要大量undo空间.
一旦发生此状况，如下方法可以用来直接清理数据而避免产生大量redo 和undo信息。
### 检查表 WRI$_ADV_OBJECTS和 Auto Stats Advisor Task相关的有效条数 ###
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=‘AUTO_STATS_ADVISOR_TASK‘);
### 创建新表并保留非 AUTO_STATS_ADVISOR_TASK数据 ###
SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=‘AUTO_STATS_ADVISOR_TASK‘);
?
### 截断原表 ###
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
?
### 把备份表中数据插回原表 ###
SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
?
### 重建索引 ###
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
?
P.S: 表 WRI$_ADV_OBJECTS_NEW在上述命令都成功执行后可删除。
从数据字典删除统计信息任务，避免再次造成问题。
DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := ‘AUTO_STATS_ADVISOR_TASK‘; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; /
?
DBA可以在任何时间重建统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)，具体参考如下办法：
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
?
统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)也可以被直接禁用：
DECLARE filter1 CLOB; BEGIN filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK‘,‘EXECUTE‘,NULL,‘DISABLE‘); END; /
?
?新的增强补丁已经被申请，将会提供更方便的方法来禁用这个任务。Unpublished Bug 26749785 NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK