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:
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);
-- 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:
Assign job to RAC instance
Assign a specific RAC instance to execute a job:
EXECUTE DBMS_JOB.INSTANCE(job=>123, instance=>1);
See created jobs:
SELECT job, what, next_date, next_sec FROM user_jobs;
Jobs that are currently running:
SELECT * FROM user_jobs_running;