Enable and Disable DBMS JOB in Oracle

Oracle will not attempt to execute the job until the broken status has been removed or the the job has been forced to execute.
Job is broken by oracle automatically if it continue failed 16 attempts, then oracle marked the job as broken. Then Oracle never attempt to execute until its status not changed.

1. Check the broken job with column BROKEN present in dba_jobs view:

Select job,what,BROKEN from dba_jobs;

2. Remove the broken status of DBMS JOB into running mode:

exec dbms_job.broken(41,FALSE);

41--> it is the job id

3. Force the job to execute by run procedure:
To force a job marked as broken, using the DBMS_JOB.BROKEN statement, the following command can be used:

EXEC DBMS_JOB.RUN(41);

41--> it is the job id

4. Verify with dba_jobs views:

Select job,what,BROKEN from dba_jobs;

5. To make the job broken, so that it is never run by Oracle Scheduler as:

exec dbms_job.broken(41,TRUE);

If you have multiple jobs running and you are continue facing the broken job issue you can scheduled the following procedure in scheduler for make broken job into running status:


CREATE OR REPLACE PROCEDURE job_fixer
AS
/*calls DBMS_JOB.BROKEN to try and set any broken jobs to unbroken*/
CURSOR broken_jobs_cur
IS
SELECT job
FROM user_jobs
WHERE broken = 'Y';
BEGIN
FOR job_rec IN broken_jobs_cur
LOOP
DBMS_JOB.BROKEN(job_rec.job,FALSE);
END LOOP;
END job_fixer;




If you like please follow and comment