Oracle：oracle 12.1.0.2 升级到12.2.0.1 后，自动任务报错：ORA-20001: Statistics Advisor: Invalid task name for the current user

 

具体错误如下：关键字：ORA-12012、ORA-20001

 

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_39628"
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

 

从网上查了查资料，主要是“惜分飞”牛人的一篇文章。

按照文章的方式尝试去解决，不行。

后来仔细研究了下，发现是自动任务所属的用户不对！

以sys用户直接删除表记录，再次按照文章的方式操作了一遍。ok！

 

----问题：alert 报错，但是 相关系统表 没有配置
select name, ctime, how_created, t.*
from   sys.wri$_adv_tasks t
where  1 = 1
      --and owner_name = 'SYS'
       and name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK');


----------------------------------------------
----解决：
delete --- 以 sys 用户执行
from   sys.wri$_adv_tasks t
where  1 = 1
      --and owner_name = 'SYS'
       and name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK');

commit;

---- oracle 也不是那么靠谱的，如果涉及复杂业务逻辑的话，比如这个问题。弱！
begin
    dbms_stats.init_package();
    ---    exec dbms_stats.init_package();
 
end;
/

begin
    dbms_auto_task_admin.DISABLE;
    dbms_auto_task_admin.ENABLE;   
end;
/


--- 再次验证。ok
----------------------------------------------
select name, ctime, how_created, t.*
from   sys.wri$_adv_tasks t
where  1 = 1
      --and owner_name = 'SYS'
       and name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK');



----------------------------------------------

select owner_name, name, ctime, how_created,t.*
from   sys.wri$_adv_tasks t
where  1 = 1
      --and owner_name = 'SYS'
       and upper(name) like '%ADVIS%'