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
If the query based on “where owner_name = ‘SYS'” condition does not show any rows but the error continues, please change the query as shown below to
see if a non-SYS user like SYSTEM owns those tasks for some reason:
$ 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.
— 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();
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;
266 views last month, 4 views today