[重慶思莊每日技術分享]-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>