Oracle Jobs - DBMS JOBS - broken fix re-run
i was working on a pl/sql application and apparently it had some jobs which were being run on some schedule. I should have just used SQL Developer's Scheduler folder tab to see what jobs were running...but i didn't think of it and ended up googling for some SQL which was:
-- sys level select * from dba_jobs -- sys level select * from dba_scheduler_jobs -- schema user level select * from user_scheduler_jobs -- schema user level select * from user_scheduler_job_run_details -- schema user level WORKED select * from user_jobs order by broken, job asc
if a job fails to successfully execute it will go into a broken state after 16 attempts, so all my jobs were broken and the scheduler was NO longer trying to re-run them. i wanted to reset that flag and execute them straight away. i wrote a loop in a stored procedure to do this.
FOR i IN (SELECT * FROM user_jobs WHERE broken = 'Y') loop num := i.job; -- i didn't want to restart some of my broken jobs if (num <> 232 and num <> 233 and num <> 241 and num <> 270) then -- true does the opposite dbms_job.broken(i.job, false); -- run the job one time, if it's broken throws an error dbms_job.run(i.job); end if; END LOOP;
