一、问题
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 from sys.wri$_adv_tasks 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
