Oracle 12c自动统计信息任务报错ORA-20001

适用范围
Oracle Database 12.2

问题概述
Oracle 12.2在每日维护窗口执行自动收集统计信息任务失败，报ORA-20001: Statistics Advisor: Invalid task name for the current user

问题原因
自动任务advisory package的异常，不能正常调用任务。与Bug27774706高度相似。
解决方案
应用补丁27774706后重建advisory package。
分析过程

1、检查数据库日志

ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47209
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47199

自动任务执行期间报ORA-20001: Statistics Advisor任务名无效。
2、检查相关任务

SQL>
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
no rows selected

检查AUTO_STATS_ADVISOR_TASK和INDIVIDUAL_STATS_ADVISOR_TASK 2个任务，
如果没有返回值，应用补丁27774706后重建advisory package.

SQL> col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATED
—————————— ——— ——————————
AUTO_STATS_ADVISOR_TASK       26-NOV-25 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 26-NOV-25 CMD

AUTO_STATS_ADVISOR_TASK和INDIVIDUAL_STATS_ADVISOR_TASK 2个任务如果存在删除后再重建。
3、应用补丁
应用补丁27774706 ，补丁应用后会有with error，可以执行以下语句

UPDATE dba_registry_sqlpatch
SET status = 'SUCCESS', action_time = SYSTIMESTAMP
WHERE patch_id = 27774706 and status='WITH ERRORS' and rownum=1;

COMMIT;

4、修复advisory package
4.1 UTO_STATS_ADVISOR_TASK和INDIVIDUAL_STATS_ADVISOR_TASK不存在的情况

$ sqlplus / as sysdba
drop table WRI$_ADV_DEFINITIONS;
@?/rdbms/admin/catadvtb.sql;
@?/rdbms/admin/utlrp.sql;
execute dbms_advisor.setup_repository;
EXEC dbms_stats.init_package();

4.2 UTO_STATS_ADVISOR_TASK和INDIVIDUAL_STATS_ADVISOR_TASK存在的情况

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected

执行package重建

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

进行验证

SQL> col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME CTIME HOW_CREATED
—————————— ——— ——————————
AUTO_STATS_ADVISOR_TASK       26-NOV-25 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 26-NOV-25 CMD

advisory package重建完成。

-the end-