From Oracle FAQ
Jump to: navigation, search

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[edit]

PL/SQL to submit a new job:

  l_job NUMBER := 0;

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[edit]

To force immediate job execution:

EXEC dbms_job.run(job_no);

Mark a job as not broken[edit]

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[edit]

Delete the job:


Assign job to RAC instance[edit]

Assign a specific RAC instance to execute a job:

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

Monitor jobs[edit]

See created jobs:

SELECT job, what, next_date, next_sec FROM user_jobs;

Jobs that are currently running:

SELECT * FROM user_jobs_running;