ORA-12012: error on auto execute of job “SYS“.“ORA$AT_OS_OPT_SY_xx“解决办法

最新推荐文章于 2024-09-20 09:14:57 发布

挠背小能手

最新推荐文章于 2024-09-20 09:14:57 发布

阅读量1.4k

点赞数

分类专栏： Oracle日常运维文章标签： oracle

本文链接：https://blog.csdn.net/m0_37625564/article/details/116376230

版权

Oracle日常运维专栏收录该内容

129 篇文章 18 订阅

订阅专栏

数据库版本：

SQL> col banner for a100;
SQL> set linesize 1000;
SQL> select * from v$version;

BANNER                                                                                                   CON_ID
---------------------------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production                                  0
PL/SQL Release 12.2.0.1.0 - Production                                                                        0
CORE    12.2.0.1.0      Production                                                                                    0
TNS for Linux: Version 12.2.0.1.0 - Production                                                                0
NLSRTL Version 12.2.0.1.0 - Production                                                                        0

alert日志出现报错：

2021-04-11T07:16:15.862335+08:00
Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_j000_24692.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_80177"
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

参照MOS文档：
ORA-12012 Error on auto execute of job “SYS”."ORA$AT_OS_OPT_SY_ in 12.2.0 Database version or higher release (like 18c) (Doc ID 2127675.1)

解决方法：

SQL>  EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> column name format A35
SQL> set linesize 120
SQL> 
SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
  4     and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                                CTIME
----------------------------------- -------------------
HOW_CREATED
------------------------------------------------------------------------------------------
AUTO_STATS_ADVISOR_TASK             2021-05-03 13:29:01
CMD

INDIVIDUAL_STATS_ADVISOR_TASK       2021-05-03 13:29:01
CMD

若此方法不行，参照MOS文档中另外的解决方法：

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