ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_3701"

1.异常内容

很多这种报错，基本上报错都是这种类型的。

ORCLPDB1(3):Errors in file /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_j002_1009695.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_8908"
ORA-38153: Software edition is incompatible with SQL plan management.
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 6014
ORA-06512: at "SYS.DBMS_SPM", line 2781
ORA-06512: at line 34

2.解决方案

Ⅰ、oracle 版本：

参考文档：ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c) (文档 ID 2127675.1)

MOS解释是因为CDB没有被正确创建引起的（我并没有启用CDB）init_package没有执行（创建statistics advisor的），所以当运行自动job的时候，就会报错。

Ⅱ、查询：

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');

Ⅲ、执行：

EXEC dbms_stats.init_package();

Ⅳ、再次查询第Ⅱ步，就会看到初始化成功生成的结果。

如果上面方法不行，执行以下sql：

$ sqlplus / as sysdba
 
select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
  
 
If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.
 
This was a case for one customer.
 
For example:
 
--- Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason
 
SQL> conn system/&password
 
 
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
 
connect / as sysdba
EXEC DBMS_STATS.INIT_PACKAGE();