|
As a review, the 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
The problem with this dbms_job procedure
is that while we specify the
initial start time and
re-execution interval, we do not
see a mechanism for running the
job during predetermined hours
during the day. For example,
how do we use dbms_job to start a job at 8:00
AM, run it hourly, and then stop
at 5:00 PM?
The example about will use dbms_job to schedule
the job to run hourly, but to
get time intervals, it is
necessary to create two other
jobs, one to break the job at
5:00 PM and another to un-break
the job the following morning at
8:00 AM.
For advanced scheduling purposes
we can create customized
job intervals using dbms_job such that start and
stop at specified intervals.
Here are some working Oracle dbms_job scheduling frequency
examples:
The
following examples show how to
use these procedures to schedule
the my_job_proc procedure to run
immediately, then once every
hour after that:
BEGIN
DBMS_JOB.isubmit (
job => 99,
what => 'my_job_proc(''DBMS_JOB.ISUBMIT
Example.'');',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24 /* 1
Hour */');
COMMIT;
END;
/
--
-- 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, 'BEGIN
statspack_alert_proc; END;',
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,
'BEGIN statspack_alert_proc;
END;',
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,
'BEGIN statspack_alert_proc;
END;',
trunc(sysdate+1)+6/24,
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);
--
----------------------------------------------------------
-- Submit job
to begin at 0600 and run
every hour, Monday - Friday
--
---------------------------------------------------------
dbms_job.submit(
:jobno,
'BEGIN statspack_alert_proc;
END;',
trunc(sysdate+1)+6/24,
trunc(
least(
next_day(SYSDATE - 1,'MONDAY'),
next_day(SYSDATE - 1,'TUESDAY'),
next_day(SYSDATE - 1,'WEDNESDAY'),
next_day(SYSDATE - 1,'THURSDAY'),
next_day(SYSDATE - 1,'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
|
