Skip to content

How to Resolve ORA-12012: error on auto execute of job

ORA-12012 ORA-20001 ORA-06512

Saw an error stack containing ORA-12012, ORA-20001 and ORA-06512 in the alert log.

Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL2/trace/ORCL2_j001_3231.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_1173"
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

It seems that the task name of advisor's is invalid and cannot be recognized at this moment.

After doing some research, it's identified as a known bug 25710407. Oracle addresses the problem in the Oracle documentation and provides a workaround.

Next, we follow the steps in the Oracle documentation.

Check Advisor's Task

SQL> show user
USER is "SYS"
SQL> column name format a30;
SQL> column how_created format a11;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected

There's none.

Initiate Stats Package

SQL> exec sys.dbms_stats.init_package();

PL/SQL procedure successfully completed.

Check Advisor's Task Again

SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                           CTIME               HOW_CREATED
------------------------------ ------------------- -----------
AUTO_STATS_ADVISOR_TASK        2023-11-10 20:02:54 CMD
INDIVIDUAL_STATS_ADVISOR_TASK  2023-11-10 20:02:54 CMD

Now we have the tasks back and no such error stack in the alert log anymore.

Leave a Reply

Your email address will not be published. Required fields are marked *