ORA-12012: error on auto execute of job

I got ” ORA-12012: error on auto execute of job ” error in Oracle database.

ORA-12012: error on auto execute of job

Details of error are as follows.

ORA-12012: Error on Auto Execute of Job

Cause: An error was caught while doing an automatic execution of a job.

Action: Look at the accompanying errors for details on why the execute failed.

error on auto execute of job

This ORA-12012 errors are related with the error was caught while doing an automatic execution of a job.

Seed database was most likely not created right by package dbms_stats.init_package not being ran.

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.

To solve this error, Run dbms_stats.init_package()  in the container database to create the tasks correctly:

$ sqlplus / as sysdba

 EXEC dbms_stats.init_package();




column name format A35
set linesize 120

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

Output of the query will look like this:

NAME                                 CTIME      HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD
 

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

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

Second case is as follows:

• Alert during auto optimizer stats collection shows following errors:
  ORA-12012: error on auto execute of job “SYS”.”ST$AUTO5548_312_B49″
  ORA-20005: object statistics are locked (stattype = ALL)
  ORA-06512: at “SYS.DBMS_STATS”, line 34850
  ORA-06512: at line 1

This is due to following bug that is still being investigated:

The sql tuning advisor job which is likely causing the conflict here.
It is the stats verification query issued by autosqltune.

Auto sqltune, tunes top sqls in maintenance window and create sql profiles.
In this process, it checks if the objects in the sqls have accurate stats by
issuing the sqls mentioned in the beginning of bug description.

Disable auto tuning job and run manually when required:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )