Oracle下查看dbms_job、dbms_scheduler_job创建的任务计划的job

 

How to find the job name if a scheduled job fails with ORA-12012

When a scheduled job fails, the alert log shows an error message like:
"ORA-12012 error on auto execute of job 47544".

How do I correlate the job number value in the alert log to the job name
listed in dba_scheduler_jobs?

Solution
The usage of finding out job information for Scheduler Jobs is different than the usage of the old Job mechanism.

In both cases ( DBMS_SCHEDULER and DBMS_JOB) the alert.log will show you an error when the job failed.

Example:
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_3036.trc:
ORA-12012: error on auto execute of job 54887
ORA-06550: line ORA-06550: line 1, column 417:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 405:
PL/SQL: SQL Statement ignored
, column :

where the number in the error message is the job identifier.

For DBMS_JOB usage:
To find out more information about that failing job you can simply go over the jobnumber and select the needed information from the view dba_jobs.

SQL>select job, what from dba_jobs ;
JOB         WHAT
---------- ----------------------------------------------------------
1           EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
21         BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END;
22         BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END;

Note: The job does not have a job_name when dbms_job is used.

 

For DBMS_SCHEDULER usage:
For the application user the usage of scheduled jobs is now more defined by the name of the job and not by the job identifier.
Unfortunal the error message ORA-12012 will not show you the name of the job, but will still show you the job identifier.
This job identifier is now more dictionary information and correlates to the object_id from the view dba_objects

The job identifier is stored in the table sys.scheduler$_job under column obj#.

When a relation for the job identifier to the job name is needed then the following select statement can help:

 

select obj# , object_name from sys.scheduler$_job , dba_objects
where obj# = object_id;

 

Example:

SQL> select obj# , object_name from sys.scheduler$_job   ssj,  dba_objects  dob
           where ssj.obj# =dob. object_id;

        OBJ#        OBJECT_NAME
       ----------     ----------------------------
       54888         RUNTEST_JOB
       54869         RUNTEST_JOB

 

Having the name of the job you can select the relevant scheduler views like DBA_SCHEDULER_JOB_RUN_DETAILS
to find out more infomation like the status , or the additional information for the scheduled job.

As an alternative, the generated job trace file can be viewed to find out the related job name:

Example:
orcl_j000_3036.trc:
------------------------
....
*** ACTION NAME:(RUNTEST_JOB) 2008-10-21 09:14:30.165
*** MODULE NAME:(DBMS_SCHEDULER) 2008-10-21 09:14:30.165
*** SERVICE NAME:(SYS$USERS) 2008-10-21 09:14:30.165
*** SESSION ID:(145.37) 2008-10-21 09:14:30.165
*** 2008-10-21 09:14:30.165
ORA-12012: error on auto execute of job 54887
ORA-06550: line ORA-06550: line 1, column 417:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 405:
PL/SQL: SQL Statement ignored
, column :
where the ACTION NAME is the name of the job.

 

sms_j000_15866.trc

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

*** ACTION NAME:(IVR_DAY_REPORT_ST) 2011-08-16 05:38:00.021
*** MODULE NAME:(DBMS_SCHEDULER) 2011-08-16 05:38:00.021
*** SERVICE NAME:(SYS$USERS) 2011-08-16 05:38:00.021
*** SESSION ID:(830.30052) 2011-08-16 05:38:00.021
*** 2011-08-16 05:38:00.021
ORA-12012: error on auto execute of job 506164
ORA-02081: database link is not open