Oracle dbms_job example

As a review, the Oracle dbms_job.submit procedure accepts three parameters, the name of the job to submit, the start time for the job, and the interval to execute the job:

dbms_job.submit(
what=>'statspack_alert.sql;',
next_date=>sysdate+1/24, -- start next hour
interval=>'sysdate+1/24'); -- Run every hour

For advanced scheduling purposes we can create customized intervals such that start and stop at specified intervals. Here are some working Oracle dbms_job scheduling frequency examples:

--
-- Schedule a snapshot to be run on this instance every hour

variable jobno number;
variable instno number;
begin

select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 'statspack.snap;',
trunc(sysdate)+6/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+9/24,
'trunc(SYSDATE+12/24,''HH'')',
TRUE,
:instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/144,'MI'),
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE,''MONDAY''),
next_day(SYSDATE,''TUESDAY''),
next_day(SYSDATE,''WEDNESDAY''),
next_day(SYSDATE,''THURSDAY''),
next_day(SYSDATE,''FRIDAY'')
)
+1/24,''HH'')',
TRUE,
:instno);

commit;
end;
/
 

For more information on Oracle DBMS_JOB see the following links:

Oracle DBMS_JOB.BROKEN
Oracle DBMS_JOB.REMOVE
Oracle DBMS_JOB vs. Oracle DBMS_SCHEDULER
Advanced Job Scheduling with Oracle dbms_job
The DBMS_JOB Package
DBMS_JOB Package Tips
Migrating from dbms_job to dbms_scheduler
Data Dictionary Views Related to dbms_job
Overview and Examples of dbms_job Functions
Oracle dbms_job example

For more dbms_job tips see: Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan).  Buy direct from the publisher and save 30%!