12.2.0.1 后,自动任务报错:ORA-20001: Statistics Advisor: Invalid task name for the current user

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

 

上一篇:scrapy 爬虫中间件 deepth深度


下一篇:(数据科学学习手札23)决策树分类原理详解&Python与R实现