[重庆思庄每日技术分享]-ORA-12012: 自动执行作业 “SYS“.“ORA$AT_OS_OPT_SY_21“ 出错

oracle 12.2.0.1版本报错：

Errors in file /u01/app/oracle/diag/rdbms/easdb/easdb2/trace/easdb2_j000_27520.trc:
ORA-12012: 自动执行作业 "SYS"."ORA$AT_OS_OPT_SY_21" 出错
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: 在 "SYS.DBMS_STATS", line 47207
ORA-06512: 在 "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: 在 "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: 在 "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: 在 "SYS.DBMS_STATS", line 47197

错误是在执行统计信息分析的时由于SCHEDULER的task name无效.

解决方法：

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‘); 2 3 4

no rows selected

Elapsed: 00:00:00.03
SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.65
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‘); 2 3 4

NAME
------------------------------------------------------------------------------------------------------------------------
CTIME HOW_CREATED
------------ ------------------------------------------------------------
AUTO_STATS_ADVISOR_TASK
13-JUN-18 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
13-JUN-18 CMD

Elapsed: 00:00:00.01
SQL>