The
create_schedule
procedure defines the start time, end time and
interval that can be applied to a job.
PROCEDURE create_schedule
(
schedule_name
IN VARCHAR2,
start_date
IN TIMESTAMP WITH TIME ZONE
DEFAULT NULL,
repeat_interval
IN VARCHAR2,
end_date
IN TIMESTAMP WITH TIME ZONE
DEFAULT NULL,
comments
IN VARCHAR2
DEFAULT NULL)
The parameters associated with this procedure
and their usage are as follows:
-
schedule_name
- A name that uniquely identifies the
schedule
-
start_date
- The date when this schedule will take
effect.
This date may be in the future if
scheduled jobs are set up in advance.
-
repeat_interval
-
The definition of how often the job should
execute.
A value of NULL indicates that the
job should only run once. The repeat
interval is defined using a calendaring
syntax, which is new to Oracle 10g.
This will be explained in more detail
later.
-
end_date
-
The date when this schedule will stop.
This, combined with the
start_date parameter, enables
a job to be scheduled for a finite period of
time.
-
comments
- Free text, allowing the user to record
additional information.
The following code segment defines a new
schedule that runs every hour on minute “0”.
The lack of an
end_date
parameter value means that the job will repeat
forever based on the interval.
BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name
=> 'test_hourly_schedule',
start_date
=> SYSTIMESTAMP,
repeat_interval => 'freq=hourly;
byminute=0',
end_date
=> NULL,
comments
=> 'Repeats hourly, on the hour, for
ever.');
END;
/
Schedules are created in the OEM DB Control via
the Create Schedule screen shown in Figure
11.16.
Figure 11.16 –
OEM DB Control: Create Schedule
Information about schedules can be displayed
using the
dba_scheduler_schedules
view.
The following script uses this view to
display information about schedules for a
specified user or all users.
--
*************************************************
-- Parameters:
--
1) Specific USERNAME or ALL which
does not limit output.
--
*****************************************************************
set verify off
select
owner,
schedule_name,
repeat_interval
from
dba_scheduler_schedules
where
owner = decode(upper('&1'), 'ALL',
owner, upper('&1'))
;
The following is an example of output from the
schedules.sql
script:
OWNER
SCHEDULE_NAME
REPEAT_INTERVAL
---------------------------------------------------------------------
JOB_USER
TEST_HOURLY_SCHEDULE
freq=hourly; byminute=0
Alternately, the Scheduler Schedules screen of
the OEM DB Control, shown in Figure 11.17, can
be used to display schedule information.
Figure 11.17 –
OEM DB Control: Scheduler Schedules
Schedules
can be dropped using the
drop_schedule
procedure, whose call specification is listed
below.
PROCEDURE drop_schedule (
schedule_name
IN VARCHAR2,
force
IN BOOLEAN
DEFAULT FALSE)
The parameters associated with this procedure
and their usage are as follows:
-
schedule_name
- A name that identifies a single schedule
or a comma separated list of schedule names.
-
force
- If set to TRUE, all jobs and windows which
reference this schedule are disabled prior
to the schedule being dropped.
If set to FALSE, the presence of
dependants will produce errors.
The following examples show how the
drop_schedule
procedure
is used:
BEGIN
DBMS_SCHEDULER.drop_schedule (schedule_name
=> 'TEST_HOURLY_SCHEDULE');
END;
/
The output from the
schedules.sql
script shows that the schedule has been removed.
no rows selected
Now that details on how to define reusable
objects such as programs and schedules have been
presented, the following section will show how
they are used to schedule jobs.
