|
The DATE
datatype is used by Oracle to
store all datetime information
in which a precision greater
than one second is not needed.
Oracle uses a seven byte binary
date format which allows Julian
dates to be stored within the
range of 01-Jan-4712 BC to
31-Dec-4712 AD. Table 3.1 shows
how each of the seven bytes is
used to store the date
information:
|
BYTE |
MEANING |
NOTATION |
EXAMPLE
(10-JUL-2004
17:21:30) |
|
1 |
Century |
Divided by 100,
excess-100 |
120 |
|
2 |
Year |
Modulo 100, excess-100 |
104 |
|
3 |
Month |
0
base |
7 |
|
4 |
Day |
0
base |
10 |
|
5 |
Hour |
excess-1 |
18 |
|
6 |
Minute |
excess-1 |
22 |
|
7 |
Second |
excess-1 |
31 |
Table 3.1 -
How each of the seven bytes
is used to store the date
information
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 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 start a job at 8:00
AM, run it hourly, and then stop
at 5:00 PM?
The example about will 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:0 AM.
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:
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;
/
|
