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.

Note: starting with version 19c, you need the CREATE JOB privilege to use the DBMS_JOB package.

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);

To automate:

-- Create the job (with a user having the EXECUTE privilege on SYS.DBMS_IJOB granted to it)...
create or replace procedure keep_jobs_unbroken as
begin
  for c1 in (select log_user, job from dba_jobs where broken = 'Y') loop
    dbms_output.put_line('Resume broken job '||c1.job||' (owner: ||c1.log_user||')');
    sys.dbms_ijob.broken(c1.job, false); -- Reactivate job if broken
  end loop;
end;
/
show errors

-- Schedule the job...
declare
  v_job number;
begin
  select job into v_job from user_jobs where what like '%keep_jobs_unbroken%';
  dbms_job.remove(v_job);
  dbms_job.submit(v_job, 'keep_jobs_unbroken;', sysdate, 'sysdate+1/24'); -- Run every hour
  dbms_job.run(v_job);
  dbms_output.put_line('Job '||v_job||' re-submitted.');
exception
  when NO_DATA_FOUND then
    dbms_job.submit(v_job, 'keep_jobs_unbroken;', sysdate, 'sysdate+1/24'); -- Run every hour
    dbms_job.run(v_job);
    dbms_output.put_line('Job '||v_job||' submitted.');
end;
/

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;