Viewing and Managing Job Queues in Oracle 9i - Page 5

Ajay Gursahani, ajay.gursahani@mahindrabt.com

DBMS_JOB.BROKEN

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:

1. Oracle has failed to successfully execute the job after 16 attempts or
2. You have marked the job as broken using the procedure DBMS_JOB.BROKEN

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.

Example:

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.

DBMS_JOB.RUN

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

Example:

execute dbms_job.run(4);

This will force a job number 4 to be executed.

Summary

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.