 |
|
Oracle Scheduling Create Schedules
Oracle Tips by Burleson Consulting |
Schedules
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 in a later chapter.
* 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 10g DB Control
via the Create Schedule screen shown in Figure 2.6.
Figure 2.6 ? OEM 10g 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.
*
schedules.sql
--
-- Parameters:
-- 1) Specific USERNAME or ALL which doesn't 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.
SQL>
@schedules job_user
OWNER
SCHEDULE_NAME
------------------------------ ------------------------------
REPEAT_INTERVAL
-------------------------------------------------------------
JOB_USER
TEST_HOURLY_SCHEDULE
freq=hourly; byminute=0
1 row
selected.
Alternately, the Scheduler Schedules screen of
the OEM 10g DB Control, shown in Figure 2.7, can be used to display
schedule information.
Figure 2.7 ? OEM 10g 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.
SQL>
@schedules job_user
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.
Jobs
Jobs are what the scheduler is all about.
They are created using the create_job procedure, which is overloaded
allowing a job to be defined in one of four ways:
* Completely self-contained, with the program
and schedule defined inline.
* Referencing both a predefined program and
schedule.
* Referencing a predefined program, but with an
inline schedule.
* Referencing a predefined schedule, but with
an inline program.
The overloaded call specifications are listed
below.
PROCEDURE
create_job (
job_name
IN VARCHAR2,
job_type
IN VARCHAR2,
job_action
IN VARCHAR2,
number_of_arguments IN PLS_INTEGER
DEFAULT 0,
start_date
IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN
VARCHAR2
DEFAULT NULL,
end_date
IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class
IN VARCHAR2
DEFAULT 'DEFAULT_JOB_CLASS',
enabled
IN BOOLEAN
DEFAULT FALSE,
auto_drop
IN BOOLEAN
DEFAULT TRUE,
comments
IN VARCHAR2 DEFAULT NULL)
PROCEDURE
create_job (
job_name
IN VARCHAR2,
program_name
IN VARCHAR2,
schedule_name
IN VARCHAR2,
job_class
IN VARCHAR2
DEFAULT 'DEFAULT_JOB_CLASS',
enabled
IN BOOLEAN
DEFAULT FALSE,
auto_drop
IN BOOLEAN
DEFAULT TRUE,
comments
IN VARCHAR2
DEFAULT NULL)
PROCEDURE
create_job (
job_name
IN VARCHAR2,
program_name
IN VARCHAR2,
start_date
IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval
IN VARCHAR2
DEFAULT NULL,
end_date
IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class
IN VARCHAR2
DEFAULT 'DEFAULT_JOB_CLASS',
enabled
IN BOOLEAN
DEFAULT FALSE,
auto_drop
IN BOOLEAN
DEFAULT TRUE,
comments
IN VARCHAR2 DEFAULT NULL)
PROCEDURE
create_job (
job_name
IN VARCHAR2,
schedule_name
IN VARCHAR2,
job_type
IN VARCHAR2,
job_action
IN VARCHAR2,
number_of_arguments IN PLS_INTEGER
DEFAULT 0,
job_class
IN VARCHAR2
DEFAULT 'DEFAULT_JOB_CLASS',
enabled
IN BOOLEAN
DEFAULT FALSE,
auto_drop
IN BOOLEAN
DEFAULT TRUE,
comments
IN VARCHAR2
DEFAULT NULL)
The parameters associated these procedures and
their usage are as follows:
* job_name - A name that uniquely identifies
the job.
* job_type - The type of action associated with
this job (PLSQL_BLOCK, STORED_PROCEDURE or EXECUTABLE).
* job_action - The actual work that is done by
the job.
* number_of_arguments - The number of arguments
required by this job. Programs which use arguments must have
their arguments defines before they can be enabled.
* start_date - The date when this schedule will
take effect. This may be in the future if scheduled jobs are
setup 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 PL/SQL
expression or the calendaring syntax, which is new to Oracle 10g.
This will be explained in more detail in a later chapter.
* 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.
* job_class - The job class associated with
this job. If no job_class is defined, the DEFAULT_JOB_CLASS is
assigned.
* enabled - A flag which indicates if the job
is enabled or not. If the job accepts arguments, it cannot be
enabled until the arguments are defined.
* auto_drop - Indicates if the job should be
dropped once it has run for the last time.
* comments - Free text, allowing the user to
record additional information.
* schedule_name - The name of the schedule,
window or window group used to define the job schedule.
* program_name - The name of the program which
defines the action of the job.
The following code examples rely on the
previously defined programs and schedules to show how the overloads
of the create_job procedure are used.
BEGIN
-- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled
=> TRUE,
comments => 'Job
defined entirely by the CREATE JOB procedure.');
END;
/
BEGIN
-- Job defined by an existing program and schedule.
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_sched_job_definition',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an
existing program and schedule.');
END;
/
BEGIN
-- Job defined by an
existing program and inline schedule.
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_job_definition',
program_name => 'test_plsql_block_prog',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled =>
TRUE,
comments => 'Job
defined by existing program and inline schedule.');
END;
/
BEGIN
-- Job defined by existing schedule and inline program.
DBMS_SCHEDULER.create_job (
job_name => 'test_sched_job_definition',
schedule_name => 'test_hourly_schedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
enabled => TRUE,
comments => 'Job defined
by existing schedule and inline program.');
END;
/
The generate_job_name function can be used to
generate a unique name for a job.
FUNCTION
generate_job_name (
prefix IN VARCHAR2
DEFAULT 'JOB$_') RETURN VARCHAR2
A sequence number is appended to the specified
job name prefix to guarantee uniqueness. If the prefix is not
specified, a standard prefix is used. The query below shows
how it can be used:
column
job_name_1 format a20
column job_name_2 format a20
select
DBMS_SCHEDULER.generate_job_name ('test_job') as job_name_1,
DBMS_SCHEDULER.generate_job_name as job_name_2
from
dual;
JOB_NAME_1
JOB_NAME_2
-------------------- --------------------
TEST_JOB6
JOB$_7
1 row
selected.
Figures 2.8 and 2.9 show the Create Job
(General) and Create Job (Schedule) screens respectively.
These provide a web-based alternative to the create_job procedure.
Figure 2.8 ? OEM 10g DB Control: Create Job
(General)
Figure 2.9 ? OEM 10g DB Control: Create Job
(Schedule)
 |
This is an excerpt from the book "Oracle
Job Scheduling" by Dr. Tim Hall. You can buy it direct
from the publisher for 30%-off and get instant access to the
code depot of Oracle job scheduling scripts. |
and include the URL for the page.
