|
Answer: 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
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;
/
|
