oraclejobsdbms-job

Oracle: how do I take a Job offline from PL/SQL?

I need to look for a JOB by thw WHAT column. Check if it is actually running. If not take it offline and put online again to make it start immediately.

Solution

  • Assuming based on the column names you referenced that you are using the DBMS_JOB package to schedule your jobs rather than the newer and more sophisticated DBMS_SCHEDULER, it's not obvious to me that you really need to take the job offline. If you want to force it to run immediately,

    dbms_job.run( <<job number>> );

    If you really do want to take the job offline, you can break it

    dbms_job.broken( <<job number>>, true );
commit;

    and then you can unbreak it

    dbms_job.broken( <<job number>>, false );
commit;

    You can determine whether the job is currently running by querying the DBA_JOBS_RUNNING view

    SELECT count(*)
  FROM dba_jobs_running
 WHERE job = <<job number>>