Oracle19c手动清理PDB SYSAUX中的大对象如WRI$_ADV_OBJECTS (ORA-65040)

遇到类似问题，转下参考：

近期一客户19c RAC CDB数据库的SYSAUX表空间增长超大，分析原因为Optimizer statistics advisor特性导致的WRI$_ADV_OBJECTS对象记录数变多， 以下为清理方法。

1, 找出最大对象

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;

或
prompt
prompt 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表空间。

2，统计记录数

SQL> col task_name format a35
SQL> select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;

3, 手动清理 如WRI$_ADV_OBJECTS

-- 删除Statistics Advisor 任务
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_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 database

SQL> ho oerr ora 65040
65040, 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.

4. 为了减少advisor存储，可以减少保留期限

-确认当前设定的保持期间
select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
where 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中相互独立。

5，禁用AUTO_STATS_ADVISOR_TASK

如果觉的这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)