DBMS JOB

From Oracle FAQ

DBMS JOB is a job scheduler package. Users on Oracle 10g and above should use the newer DBMS_SCHEDULER package.

Contents 1 Submit a job 2 Run a job now 3 Mark a job as not broken 4 Remove a submitted job 5 Assign job to RAC instance 6 Monitor jobs

Submit a job

PL/SQL to submit a new job:

DECLARE
  l_job NUMBER := 0;
BEGIN
  DBMS_JOB.SUBMIT(l_job,'procedure_name;',sysdate,TRUNC(sysdate)+1+1/288);
END;
/

Or, submit it from SQL*Plus. Example:

var jobno number
exec dbms_job.submit(:jobno, 'begin null; end;');
print jobno

Run a job now

To force immediate job execution:

EXEC dbms_job.run(job_no);

Mark a job as not broken

If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset, issue command:

EXEC dbms_job.broken(jobno, FALSE);

Remove a submitted job

Delete the job:

EXECUTE DBMS_JOB.REMOVE(jobno);

Assign job to RAC instance

Assign a specific RAC instance to execute a job:

EXECUTE  DBMS_JOB.INSTANCE(job=>123, instance=>1);

Monitor jobs

See created jobs:

SELECT job, what, next_date, next_sec FROM user_jobs;

Jobs that are currently running:

SELECT * FROM user_jobs_running;