Oracle12C，19c手动清理PDB SYSAUX中的大对象WRI$_ADV_OBJECTS

1、现象：

发现sysaux.dbf文件增大，

2、查看对应表及数据量：

select owner,table_name,tablespace_name,num_rows from all_tables where tablespace_name='SYSAUX' order by num_rows desc;
select count(*) from WRI$_ADV_OBJECTS;

3、分析：

发现最大对象为Wri$_adv_objects ，因为在12.2中引入了一个新功能：优化器统计顾问。 优化器统计信息顾问每天在“维护”窗口中运行，多次auto_stats_advisor_task，并占用大量sysaux表空间。再次查看语句

select * from (
select tablespace_name,topseg_seg_owner,topseg_segment_name,segment_type,mb,partitions, row_number() over(partition by tablespace_name order by mb desc) rn from (
select 
tablespace_name, 
owner topseg_seg_owner, 
segment_name topseg_segment_name, 
--partition_name, 
segment_type, 
round(SUM(bytes/1048576)) MB, 
case when count(*) >= 1 then count(*) else null end partitions 
from dba_segments 
where upper(tablespace_name) in ('SYSTEM','SYSAUX') -- tablespace name 
group by 
tablespace_name, 
owner, 
segment_name, 
segment_type ))
where rn<=10;

4、解决：

（1）清理/删除WRI$_ADV_OBJECTS相关数据

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

 ①如果遇到错误：Ora-20001:statistics advisor:invalid Task Name for the current user 执行

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

②如果 WRI$_ADV_OBJECTS 记录过多，delete以上会占用较大undo,可以把想要的数据存储在临时表，truncate table WRI$_ADV_OBJECTS,再insert回来。

truncate table WRI$_ADV_OBJECTS

重建索引

ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

（2）禁用Optimizer Statistics Advisor Task

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

（3）可以通过修改保留时间，限制保留为 15天（LLL推荐）

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 15
);
END;
/

（4）启用Optimizer Statistics Advisor Task

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

5、truncate WRI$_ADV_OBJECTS后，到第二天才发现数据有变化，不再是9000多万行。

select owner,table_name,num_rows from all_tables where table_name='WRI$_ADV_OBJECTS';