Database Reference
In-Depth Information
Table 18-1. DBMS_JOB Metadata
Variable
Name
Data Type
Description
job
BINARY_INTEGER
Job number of the job currently being executed.
next_date
DATE
Date and time of the next scheduled job run; this variable
may be used to override the repeat interval of a job.
broken
BOOLEAN
The value of this variable is initially FALSE , irrespective of the
current job status. It may be used to mark a job as broken. If
this variable is assigned the value TRUE and the job completes
without raising an exception, the job is marked as broken
( ALL_JOBS.BROKEN='Y' ).
The variable broken is provided for jobs that catch exceptions. The job queue cannot
detect failing jobs if a job catches exceptions internally. Setting broken=TRUE in an anonymous
PL/SQL block that implements a job allows a job developer to mark the job as broken if an
exception is caught. Thus, he may set the job's status according to his own strategy. Jobs that
do raise exceptions are marked as broken after 16 failures. Job queue processes use an exponential
backoff strategy for calculating the next scheduled run date of failed jobs. Jobs are reattempted
one minute after the initial failure. The wait interval is doubled after each additional failure.
The next scheduled run of a job may be overridden by assigning a tailored value to the variable
next_date . This may be used to implement a custom strategy for reattempting failed jobs. The
source code depot contains the file dbms_job_metadata.sql with a sample implementation of a
job, which uses the three PL/SQL metadata variables in Table 18-1.
Examples
Following is a scenario for debugging a failing job in a foreign schema by running it in a fore-
ground process with DBMS_IJOB.RUN :
SQL> SHOW USER
USER is "SYS"
SQL> SELECT job, priv_user, failures FROM dba_jobs WHERE job=1;
JOB PRIV_USER FAILURES
---------- ------------ ----------
1 PERFSTAT 1
SQL> EXEC dbms_job.run(1)
BEGIN dbms_job.run(1); END;
*
ERROR at line 1:
ORA-23421: job number 1 is not a job in the job queue
SQL> EXEC sys.dbms_ijob.run(1)
 
Search WWH ::




Custom Search