Viewing and Managing Job Queues in Oracle 9i - Page 5
January 3, 2003Ajay Gursahani, firstname.lastname@example.org
Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it.
DBMS_JOB.BROKEN ( job in binary_integer, Broken in boolea, Next_date in date default sysdate);job - Job being run.
broken - TRUE or FALSE.
next_date - Date of next refresh.
A job is labeled as either broken or not broken. When you submit a job, it is considered not broken. There are two ways a job can break:
Once a job has been marked as broken, Oracle will not attempt to execute the job until you either mark the job as not broken or force the job to be executed by calling the procedure DBMS_JOB.RUN.
execute DBMS_JOB.BROKEN(4, TRUE); SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE,BROKEN,INTERVAL FROM DBA_JOBS; JOB SUBSTR(WHAT,1,35) NEXT_DATE BROKEN INTERVAL ----------------------------------------------------------------------- 1 HOUSEKEEP.DEL001; 18/12/2002 N sysdate+(59/(24*60*60)) 3 HOUSEKEEP.DEL006; 10/04/2001 N sysdate+(59/(24*60*60)) 4 DEPT.DELREC; 18/12/2002 Y sysdate+(59/(24*60*60)) 5 HOUSEKEEP.DEL004; 10/04/2001 N sysdate+(1/48) 4 rows selected.
execute DBMS_JOB.BROKEN(4, FALSE, NEXT_DAY(SYSDATE, 'FRIDAY'));
The job 4 is marked as not broken, and its next execution date is set to the following Friday.
Forces a specified job to run.
DBMS_JOB.RUN (job IN BINARY_INTEGER force IN BOOLEAN DEFAULT FALSE);job - Number of the job being run
force - If FALSE, the specified instance must be already running or else will return an error
This will force a job number 4 to be executed.
This article has discussed creating, viewing, and managing job queues in Oracle 9i for use in scheduling the periodic execution of user jobs. A number of examples have been covered to describe the various aspects of managing job queues through the DBMS_JOB package viz, including examples of submitting a job to the job queue, removing a job from the queue, altering jobs, broken jobs, and forcing jobs to execute.