DBMS JOB

DBMS JOB is a job scheduler package. Users on Oracle 10g may have advantage to use the new DBMS_SCHEDULER package. Apart from scheduling the execution of a job they do not fit the same needs.

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

Warning: you must commit the job submission otherwise the job coordinator cannot see it. As a corollary, you can cancel a not-committed job submission using the rollback statement.

DBMS_JOB jobs support complex scheduling, see DBMS_JOB complex scheduling.

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;