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 342.解决方案
Ⅰ、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();
文章评论