Fixed the broken DBMS JOB in Oracle

Handle the broken 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(1,FALSE);

3. You can also 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(52);

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(118,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;

For more Details about DBMS JOBS: DBMS JOBS More Useful commands

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s