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