本文共 4149 字，大约阅读时间需要 13 分钟。
遇到类似问题，转下参考：
近期一客户19c RAC CDB数据库的SYSAUX表空间增长超大，分析原因为Optimizer statistics advisor特性导致的WRI$_ADV_OBJECTS对象记录数变多， 以下为清理方法。
SQL> set lines 120SQL> col occupant_name format a30SQL> select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;或promptprompt List of TOP 10 largest objects in SYSTEM AND SYSAUX TABLESPACE:prompt 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; 这个客户发现是Sm/advisor和最大对象为Wri$_adv_objects ，因为在12.2中引入了一个新功能：优化器统计顾问。 优化器统计信息顾问每天在“维护”窗口中运行，多次auto_stats_advisor_task，并占用大量sysaux表空间。
SQL> col task_name format a35SQL> select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;
-- 删除Statistics Advisor 任务DECLAREv_tname VARCHAR2(32767);BEGINv_tname := 'AUTO_STATS_ADVISOR_TASK';DBMS_STATS.DROP_ADVISOR_TASK(v_tname);END;/
Note:
1, 如果遇到错误：Ora-20001:statistics advisor:invalid Task Name for the current user 执行SQL> EXEC DBMS_STATS.INIT_PACKAGE();
2, 如果 WRI$_ADV_OBJECTS 记录过多，delete以上会占用较大undo,可以把想要的数据存储在临时表，truncate table WRI$_ADV_OBJECTS,再insert回来。
–删除任务之后，重组表和所有索引
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
3， 如果在CDB中，以上操作没有任何问题，但是在PDB中MOVE表可能会有如下ORA-65040错误
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;ALTER TABLE WRI$_ADV_OBJECTS MOVE *ERROR at line 1:ORA-65040: operation not allowed from within a pluggable databaseSQL> ho oerr ora 6504065040, 00000, "operation not allowed from within a pluggable database"// *Cause: An operation was attempted that can only be performed in the root// or application root container.// *Action: Switch to the root or application root container to perform the// operation.//
解决方法有2种：
1，_oracle_scripts参数SQL> alter session set "_oracle_script"=true;Session altered.SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;Table altered.
2，dbms_pdb.exec_as_oracle_script
SQL> exec dbms_pdb.exec_as_oracle_script('alter table sys.WRI$_ADV_OBJECTS move');PL/SQL procedure successfully completed. -确认当前设定的保持期间select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERSwhere task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME like '%EXPIRE%';-- 修改设定的保持期间,如历史数据的保存时间为15天：BEGIN DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER ( task_name => 'AUTO_STATS_ADVISOR_TASK' , parameter => 'EXECUTION_DAYS_TO_EXPIRE' , value => 15);END;/
Note:
但是需要注意12.2中的Bug 26764561 AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)， 该配置在CDB和不同PDB中相互独立。如果觉的这ADVISOR实在没用，可以考虑禁用，但是12c-20c默认需要先安装一bug 26749785 patch增加AUTO_STATS_ADVISOR_TASK控制,注意这不是bug，只是增强功能。在安装该patch前（或21.1版本前），无法使用以下功能。
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')--------------------------------------------------------------------------------FALSE 或者使用
declare filter1 clob;begin filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK', 'EXECUTE', NULL, 'DISABLE');END;/ References SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
转载地址：http://xcsof.baihongyu.com/