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

最新推荐文章于 2024-09-17 21:52:37 发布

肖雪松

最新推荐文章于 2024-09-17 21:52:37 发布

阅读量4k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： Oracle 报错

本文链接：https://blog.csdn.net/xiaofeng_rui/article/details/101421726

oracle 专栏收录该内容

9 篇文章

订阅专栏

问题

新装了一套12.2.0.1的库，收到alert的告警邮件，是晚上10点多发的，报告的内容大致如下

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_88"
ORA-20001: Statistics Advisor: Invalid Task Name For the current user <== Note it
ORA-06512: at "SYS.DBMS_STATS", line 46861
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 734
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19930
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21934
ORA-06512: at "SYS.DBMS_STATS", line 46851

解决

查了一下MOS，很容易就找到了对应的文章，MOS解释是因为CDB没有被正确创建引起的（我并没有启用CDB）init_package没有执行（创建statistics advisor的），所以当运行自动job的时候，就会报错

解决办法就是手工执行下

$ sqlplus / as sysdba

 EXEC dbms_stats.init_package();

column name format A35
set linesize 120

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

 

Output of the query will look like this:
NAME                                 CTIME      HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD

其他可能的问题

If the query based on "where owner_name = 'SYS'" condition does not show any rows but the error continues, please change the query as shown below to

see if a non-SYS user like SYSTEM owns those tasks for some reason:

$ 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();