sysaux表空间爆满WRI$_ADV_OBJECTS占用严重

1.查询sysaux表空间占用情况
select d.owner, d.segment_name, bytes / 1024 / 1024, d.segment_type
from dba_segments d
where d.tablespace_name = 'SYSAUX'
order by 3 desc;

主要是由于WRI$_ADV_OBJECTS表占用空间较多导致,WRI$_ADV_OBJECTS表主要是12.2以及以上版本新特性Optimizer Statistics Advisor 功能使用到的表,用来存储相关数据。

2.清理WRI$_ADV_OBJECTS
--表越大，索引越大的执行时间越长，耐心等待
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;

3.查看当前advisor记录保留时间
select task_name,parameter_name, parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK' and parameter_name='EXECUTION_DAYS_TO_EXPIRE';

4.修改advisor记录保留事件
BEGIN
 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
 task_name =>'AUTO_STATS_ADVISOR_TASK',
 parameter =>'EXECUTION_DAYS_TO_EXPIRE',                                         value=>10);
END；
/