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 |
[edit]
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
[edit]
Run a job now
To force immediate job execution:
EXEC dbms_job.run(job_no);
[edit]
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);
[edit]
Remove a submitted job
Delete the job:
EXECUTE DBMS_JOB.REMOVE(jobno);
[edit]
Assign job to RAC instance
Assign a specific RAC instance to execute a job:
EXECUTE DBMS_JOB.INSTANCE(job=>123, instance=>1);
[edit]
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;

