12.2.0.1版本数据库报类似ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_383″错误

Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10213.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_383"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
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 47197

trace文件信息

Trace file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10379.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /u01/app/oracle/product/12.2.0/db_1
System name:    Linux
Node name:      yoridb2
Release:        3.10.0-514.26.2.el7.x86_64
Version:        #1 SMP Tue Jul 4 15:04:05 UTC 2017
Machine:        x86_64
Instance name: xifenfei
Redo thread mounted by this instance: 1
Oracle process number: 148
Unix process pid: 10379, image: oracle@yoridb2 (J000)
*** 2017-08-04T14:41:26.486692+08:00
*** SESSION ID:(508.52539) 2017-08-04T14:41:26.486716+08:00
*** CLIENT ID:() 2017-08-04T14:41:26.486722+08:00
*** SERVICE NAME:(SYS$USERS) 2017-08-04T14:41:26.486727+08:00
*** MODULE NAME:(DBMS_SCHEDULER) 2017-08-04T14:41:26.486732+08:00
*** ACTION NAME:(ORA$AT_OS_OPT_SY_384) 2017-08-04T14:41:26.486738+08:00
*** CLIENT DRIVER:() 2017-08-04T14:41:26.486743+08:00
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_384"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
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 47197

错误比较明显，是在执行统计信息分析的时候报错了，报错原因是由于SCHEDULER的task name无效.

解决方法

[oracle@yoridb2 trace]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 23:40:33 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4
no rows selected
SQL> EXEC dbms_stats.init_package();
PL/SQL procedure successfully completed.
SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4
NAME
--------------------------------------------------------------------------------
CTIME     HOW_CREATED
--------- ------------------------------
AUTO_STATS_ADVISOR_TASK
04-AUG-17 CMD
INDIVIDUAL_STATS_ADVISOR_TASK
04-AUG-17 CMD