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

jiawang

报错现象：alter日志出现大量下面错误，而且这些错误一般是晚上22点出现
Errors in file /home/u01/app/oracle/diag/rdbms/xxx/xxx/trace/xxx_j000_106602.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"
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

数据库版本信息如下所示：

SQL> select * from v$version;

BANNER CON_ID

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

Oracle Database 12c Standard 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

解决办法：

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