oracleplsqloracle10gdbms-job

Script to disable all jobs in Oracle (DBMS_JOB package)?

I'm looking for a script which disables all the jobs. Right now I highlight them all in Toad, click the take offline button and then commit changes. There has to be a way to do this in PL/SQL.

Solution

  • If you want to prevent all jobs from running, you can change the initialization parameter JOB_QUEUE_PROCESSES. If you set that to 0, Oracle won't run any jobs scheduled using DBMS_JOB.

    You could also mark the jobs broken

    BEGIN
  FOR x IN (SELECT * FROM user_jobs)
  LOOP
    dbms_job.broken( x.job, true );
  END LOOP;
END;

    which will cause them not to be run (but will allow any jobs created after that point to run normally). To unbreak the jobs

    BEGIN
  FOR x IN (SELECT * FROM user_jobs)
  LOOP
    dbms_job.broken( x.job, false, SYSDATE + interval '1' minute);
  END LOOP;
END;

    will set all the jobs to run in 1 minute.