ORA-12012: error on auto execute of job in Oracle

ORA-12012: error on auto execute of job 55536

In alert log we are getting the error, So we checked the job number which is causing the error. If it is not needed then drop it or disable it by dbms_scheduler procedure.

For checking the status of job when its last run and owner and inform to application team if its belong to application job:

select
d.job_name,
d.job_action,d.NEXT_RUN_DATE,d.OWNER
from
dba_scheduler_jobs d,
sys.scheduler$_job s
where
d.job_action = s.program_action
and
s.obj# = 55536

If error related to Auto Gather stats jobs:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_nn"

Error:

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_”

Solution
1. Run the following query for checking advisory package:

col name for a30
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

2. If no row found then initialize the package with following commands.

EXEC dbms_stats.init_package();

3. Verify the package create. It will fixed the issue.

col name for a30
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        05-DEC-18 CMD                                    
INDIVIDUAL_STATS_ADVISOR_TASK  05-DEC-18 CMD     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.