Skip Headers

PL/SQL Packages and Types Reference
10g
Release 1 (10.1)
Part Number B10802-01

Home

Book List

Contents

Index

Master Index

Feedback

Previous

Next

View PDF

83
DBMS_SCHEDULER

The DBMS_SCHEDULER package provides a collection of
scheduling functions and procedures that are callable from any PL/SQL
program.

See
Also:

Oracle
Database Administrator's Guide for more information regarding how
to use DBMS_SCHEDULER

This chapter contains the following topics:

Using
DBMS_SCHEDULER

Rules
and Limits

Summary
of DBMS_SCHEDULER Subprograms

Using
DBMS_SCHEDULER

Rules
and Limits

Rules and
Limits

The following rules apply when using the DBMS_SCHEDULER
package:

Only SYS can do
anything in SYS schema.

Several of the procedures accept
comma-delimited lists of object names. When a list of names is provided, the
Scheduler will stop executing the list on the very first object that returns
an error. This means that the Scheduler will not perform the task on the
objects in the list after the one that caused the error. For example,
DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3, sys.jobclass1,
sys.jobclass2, sys.jobclass3'); If job3 could not be
stopped, then job1 and job2 will be stopped, but the
jobs in jobclass1, jobclass2, and
jobclass3 will not be stopped.

Performing an action on an object
that does not exist returns a PL/SQL exception stating that the object does
not exist.

Summary of
DBMS_SCHEDULER Subprograms

Table 83-1  DBMS_SCHEDULER Package
Subprograms

Subprogram

Description

ADD_WINDOW_GROUP_MEMBER
Procedure

Adds a window to an existing window group

CLOSE_WINDOW
Procedure

Closes an open Scheduler window prematurely

COPY_JOB
Procedure

Copies an existing job

CREATE_JOB
Procedures

Creates a job

CREATE_JOB_CLASS
Procedure

Creates a job class

CREATE_PROGRAM
Procedure

Creates a program

CREATE_SCHEDULE
Procedure

Creates a schedule

CREATE_WINDOW
Procedures

Creates a window

CREATE_WINDOW_GROUP
Procedure

Creates a new window group

DEFINE_ANYDATA_ARGUMENT
Procedure

Defines a program argument whose value is of a complex type
and must be passed encapsulated in an AnyData
object

DEFINE_METADATA_ARGUMENT
Procedure

Defines a special metadata argument for the program. You can
retrieve specific Scheduler metadata through this argument

DEFINE_PROGRAM_ARGUMENT
Procedure

Defines a program argument whose value can be passed as a
string literal to the program

DISABLE
Procedure

Disables a program, job, window, or window group

DROP_JOB
Procedure

Drops a job or all jobs in a job class

DROP_JOB_CLASS
Procedure

Drops a job class

DROP_PROGRAM
Procedure

Drops a program

DROP_PROGRAM_ARGUMENT
Procedures

Drops a program argument

DROP_SCHEDULE
Procedure

Drops a schedule

DROP_WINDOW
Procedure

Drops a window

DROP_WINDOW_GROUP
Procedure

Drops a window group

ENABLE
Procedure

Enables a program, job, window, or window group

EVALUATE_CALENDAR_STRING
Procedure

Evaluates the calendar string and tells you what the next
execution date of a job or window will be

GENERATE_JOB_NAME
Function

Generates a unique name for a job. This enables you to
identify jobs by adding a prefix, so, for example, Sally's jobs would be
named sally1, sally2, and so on

GET_ATTRIBUTE
Procedure

Retrieves the value of an attribute of a Scheduler
object

GET_SCHEDULER_ATTRIBUTE
Procedure

Retrieves the value of a Scheduler attribute

OPEN_WINDOW
Procedure

Opens a window prematurely. The window is opened immediately
for the duration

PURGE_LOG
Procedure

Purges specific rows from the job and window logs

REMOVE_WINDOW_GROUP_MEMBER
Procedure

Removes a window from an existing window group. This fails if
the specified window is not a member of the given group

RESET_JOB_ARGUMENT_VALUE
Procedures

Resets the current value assigned to an argument defined with
the associated program

RUN_JOB
Procedure

Runs a job immediately

SET_ATTRIBUTE
Procedure

Changes an attribute of a Scheduler object

SET_ATTRIBUTE_NULL
Procedure

Changes an attribute of a Scheduler object to
NULL

SET_JOB_ANYDATA_VALUE
Procedures

Sets the value of a job argument encapsulated in an
AnyData object

SET_JOB_ARGUMENT_VALUE
Procedures

Sets the value of a job argument

SET_SCHEDULER_ATTRIBUTE
Procedure

Sets the value of a Scheduler attribute

STOP_JOB
Procedure

Stops a currently running job or all jobs in a job
class

ADD_WINDOW_GROUP_MEMBER Procedure

This procedure adds one or more windows to an existing window
group.

Syntax

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
group_name IN VARCHAR2,
window_list IN VARCHAR2);

Parameters

Table 83-2 ADD_WINDOW_GROUP_MEMBER
Procedure Parameters

Parameter

Description

group_name

The name of the window group.

window_list

The name of the window or windows.

Usage
Notes

If an already open window is added to a window group, the Scheduler
will not pick up jobs that point to this window group until the next window in
the window group opens.

Adding a window to a group requires the MANAGE
SCHEDULER privilege.

Note that a window group cannot be a member of another window
group.

CLOSE_WINDOW Procedure

This procedure closes an open window prematurely. A closed window
means that it is no longer in effect. When a window is closed, the Scheduler
will switch the resource plan to the one that was in effect outside the window
or in the case of overlapping windows to another window.

Syntax

DBMS_SCHEDULER.CLOSE_WINDOW (
window_name IN VARCHAR2);

Parameters

Table 83-3 CLOSE_WINDOW Procedure
Parameters

Parameter

Description

window_name

The name of the window.

Usage
Notes

If you try to close a window that does not exist or is not open, an
error is generated.

A job that is running will not stop when the window it is running in
closes unless the attribute stop_on_close was set to
TRUE for the job. However, the resources allocated to the job may
change because the resource plan may change.

When a running job has a window group as its schedule, the job will
not be stopped when its window is closed if another window that is also a member
of the same window group then becomes active. This is the case even if the job
has the attribute stop_on_close set to TRUE.

Closing a window requires the MANAGE
SCHEDULER privilege.

COPY_JOB
Procedure

This procedure copies all attributes of an existing job to a new
job. The new job is created disabled, while the state of the existing job is
unaltered.

Syntax

DBMS_SCHEDULER.COPY_JOB (
old_job IN VARCHAR2,
new_job IN VARCHAR2);

Parameters

Table 83-4 COPY_JOB Procedure
Parameters

Parameter

Description

old_job

The name of the existing job.

new_job

The name of the new job.

CREATE_JOB
Procedures

This procedure creates a job.

The procedure is overloaded. The different functionality of each
form of syntax is presented along with the syntax declaration.

Syntax

Creates a job in a single call without using an existing program or
schedule:

DBMS_SCHEDULER.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);

Creates a job using a named schedule object and a named program
object:

DBMS_SCHEDULER.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);

Creates a job using a named program object and an inlined
schedule:

DBMS_SCHEDULER.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);

Creates a job using a named schedule object and an inlined program
or program:

DBMS_SCHEDULER.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);

Parameters

Table 83-5  CREATE_JOB Procedure
Parameters

Parameter

Description

job_name

This attribute specifies the name of the job and uniquely
identifies the job. The name has to be unique in the SQL namespace. For
example, a job cannot have the same name as a table in a schema.

If job_name is not specified, an error is
generated. If you want to have a name generated by the Scheduler, you can
use the GENERATE_JOB_NAME procedure to generate a name and
then use the output in the CREATE_JOB procedure. The
GENERATE_JOB_NAME procedure call generates a number from a
sequence, which is the job name. You can prefix the number with a string.
The job name will then be the string with the number from the sequence
appended to it. See "GENERATE_JOB_NAME
Function"
for more information.

job_type

This attribute specifies the type of job that you are
creating. If it is not specified, an error is generated. The three
supported values are:

plsql_block

This specifies that the job is an anonymous PL/SQL block. Job
or program arguments are not supported when the job or program type is
plsql_block. In this case, the number of arguments must be 0.

stored_procedure

This specifies that the job is an external PL/SQL procedure.
By reusing PL/SQL's External Procedure feature, this can also be a Java
stored procedure or an external C routine.

executable

This specifies that the job is a job external to the database.
External jobs are anything that can be executed from the operating
system's command line.

Anydata arguments are not supported with a job or
program type of executable.

job_action

This attribute specifies the action of the job. The following
actions are possible:

For a PL/SQL block, the action is to execute PL/SQL code.
These blocks must end with a semi-colon. For example,
my_proc(); or BEGIN my_proc(); END; or
DECLARE arg pls_integer := 10; BEGIN my_proc2(arg); END;.
Note that the Scheduler wraps job_action in its own block and
passes the following to PL/SQL for execution: DECLARE ... BEGIN
job_action END; This is done to declare some internal Scheduler
variables.

For a stored procedure, the action is the name of the stored
procedure. You have to specify the schema if the procedure resides in
another schema than the job.

PL/SQL functions or procedures with INOUT or
OUT arguments are not supported as job_action
when the job or program type is stored_procedure.

For an executable, the action is the name of the external
executable, including the full path name and any command-line arguments.

If job_action is not specified, an error is
generated when creating the job.

number_of_arguments

This attribute specifies the number of arguments that the job
expects. The range is 0-255, with the default being 0.

program_name

The name of the program associated with this job.

start_date

This attribute specifies the first date on which this job is
scheduled to start. If start_date and
repeat_interval are left null, then the job is scheduled to
run as soon as the job is enabled.

For repeating jobs that use a calendaring expression to
specify the repeat interval, start_date is used as a
reference date. The first time the job will be scheduled to run is the
first match of the calendaring expression that is on or after the current
date.

The Scheduler cannot guarantee that a job will execute on an
exact time because the system may be overloaded and thus resources
unavailable.

repeat_interval

This attribute specifies how often the job should repeat. You
can specify the repeat interval by using calendaring or PL/SQL
expressions.

The expression specified is evaluated to determine the next
time the job should run. If repeat_interval is not specified,
the job will run only once at the specified start date. See Table 83-9
for further information.

schedule_name

The name of the schedule, window, or window group associated
with this job.

end_date

This attribute specifies the date after which the job will
expire and will no longer be executed. When end_date is
reached, the job is disabled. The STATE of the job will be
set to COMPLETED, and the enabled flag will be
set to FALSE.

If no value for end_date is specified, the job
will repeat forever unless max_runs or
max_failures is set, in which case the job stops when either
value is reached.

The value for end_date must be after the value
for start_date. If it is not, an error is generated when the
job is enabled.

job_class

This attribute specifies the job class that the job belongs
to. If no job class is specified, then the job is assigned to the default
class. Note that the owner of a job must have EXECUTE
privileges on a job class in order to run a job using the resources of
that class. If an invalid value for job_class is specified,
an error is generated.

comments

This attribute specifies a comment about the job. By default,
this attribute is NULL.

enabled

This attribute specifies whether the job is created enabled or
not. The possible settings are TRUE or FALSE. By
default, this attribute is set to FALSE and, therefore, the
job is created as disabled. A disabled job means that the metadata about
the job has been captured and the job exists as a database object but the
Scheduler will ignore it and the job coordinator will not pick the job for
processing. In order for the job coordinator to process the job, the job
has to be enabled. You can enable a job by setting this argument to
TRUE or by using the ENABLE procedure.

auto_drop

This flag specifies whether the job will be automatically
dropped once it has been executed for non-repeating jobs or when its
status is changed to COMPLETED for repeating jobs. The
metadata is removed from the database if this flag is set to
TRUE.

If this flag is set to FALSE, the jobs are not
dropped and their metadata is kept until the job is explicitly dropped and
can be queried using the *_SCHEDULER_JOBS views. This
metadata can be removed from the table using the DROP_JOB
procedure.

By default, jobs are created with auto_drop set
to TRUE.

Usage
Notes

Jobs are created disabled by default, thus you must explicitly
enable them so they will become active and scheduled.

To create a job in your own schema, you need to have the
CREATE JOB privilege. A user with the
CREATE ANY JOB privilege can create a job
in any schema.

Associating a job with a particular class or program requires
EXECUTE privileges for that class.

CREATE_JOB_CLASS Procedure

This procedure creates a job class. Job classes are created in the
SYS schema.

Syntax

DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name IN VARCHAR2,
resource_consumer_group IN VARCHAR2 DEFAULT NULL,
service IN VARCHAR2 DEFAULT NULL,
logging_level IN PLS_INTEGER DEFAULT NULL,
log_history IN PLS_INTEGER DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-6  CREATE_JOB_CLASS
Procedure Parameters

Parameter

Description

job_class_name

The name of the class being created. A schema other than
SYS cannot be specified.

This attribute specifies the name of the job class and
uniquely identifies the job class. The name has to be unique in the SQL
namespace. For example, a job class cannot have the same name as a table
in a schema.

resource_consumer_group

This attribute specifies the resource consumer group this
class is associated with. A resource consumer group is a set of
synchronous or asynchronous sessions that are grouped together based on
their processing needs. A job class has a many-to-one relationship with a
resource consumer group. The resource consumer group that the job class
associates with will determine the resources that will be allocated to the
job class.

If the resource consumer group that a job class is associated
with is dropped, the job class will then be associated with the default
resource consumer group.

If no resource consumer group is specified, the job class is
associated with the default resource consumer group.

If the specified resource consumer group does not exist when
creating the job class, an error occurs.

If resource_consumer_group is specified, you
cannot specify a service (it must be NULL). Also, if a
service is specified, resource_consumer_group must be
NULL.

service

This attribute specifies the service the job class belongs to.
The service that a job class belongs to specifies that the jobs in this
class will have affinity to the particular service specified. In a RAC
environment, this means that the jobs in this class will only run on those
database instances that are assigned to the specific service.

If the service that a job class belongs to is dropped, the job
class will then belong to the default service.

If no service is specified, the job class will belong to the
default service, which means it will have no service affinity and any one
of the database instances within the cluster might run the job.

If the specified service does not exist when creating the job
class, then an error occurs.

logging_level

This attribute specifies how much information is logged. The
three possible options are:

DBMS_SCHEDULER.LOGGING_OFF

No logging will be performed for any jobs in this class.

DBMS_SCHEDULER.LOGGING_RUNS

The Scheduler will write detailed information to the job log
for all runs of each job in this class.

DBMS_SCHEDULER.LOGGING_FULL

In addition to recording every run of a job, the Scheduler
will record all operations performed on all jobs in this class. In other
words, every time a job is created, enabled, disabled, altered, and so on
will be recorded in the log.

log_history

This enables you to control the amount of logging the
Scheduler performs. To prevent the job log and the window log from growing
indiscriminately, the Scheduler has an attribute that specifies how much
history (in days) to keep. Once a day, the Scheduler will automatically
purge all log entries from both the job log as well as the window log that
are older than the specified history. The default is 30 days.

You can change the default by using the
SET_SCHEDULER_ATTRIBUTE procedure. For example, to change it
to 90 days, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('log_history','90');

The range of valid values is 1 through 999.

comments

This attribute is for an optional comment about the job class.
By default, this attribute is NULL.

Usage
Notes

For users to create jobs that belong to a job class, the job owner
must have EXECUTE privileges on the job class. Therefore, after the
job class has been created, EXECUTE privileges must be granted on
the job class so that users create jobs belonging to that class. You can also
grant the EXECUTE privilege to a role.

Creating a job class requires the MANAGE
SCHEDULER system privilege.

CREATE_PROGRAM Procedure

This procedure creates a program.

Syntax

DBMS_SCHEDULER.CREATE_PROGRAM (
program_name IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
enabled IN BOOLEAN DEFAULT FALSE,
comments IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-7  CREATE_PROGRAM
Procedure Parameters

Parameter

Description

program_name

This attribute specifies a unique identifier for the program.
The name has to be unique in the SQL namespace. For example, a program
cannot have the same name as a table in a schema. If no name is specified,
then an error occurs.

program_type

This attribute specifies the type of program you are creating.
If it is not specified then you will get an error. There are three
supported values for program_type:

plsql_block: This specifies that the program is a
PL/SQL block. Job or program arguments are not supported when the job or
program type is plsql_block. In this case, the number of
arguments must be 0.

stored_procedure: This specifies that the program
is a stored procedure. It can be a PL/SQL, Java, or a C routine outside
the database. PL/SQL functions or procedures with INOUT or
OUT arguments are not supported as job_action
when the job or program type is stored_procedure.

executable: This specifies that the program is
external to the database. External programs implies anything that can be
executed from the operating system's command line. AnyData
arguments are not supported with job or program type executable

program_action

This attribute specifies the action of the program. The
following actions are possible:

For a PL/SQL block, the action is to execute PL/SQL code.
These blocks must end with a semi-colon. For example,
my_proc(); or BEGIN my_proc(); END; or
DECLARE arg pls_integer := 10; BEGIN my_proc2(arg); END;.
Note that the Scheduler wraps job_action in its own block and
passes the following to PL/SQL for execution: DECLARE ... BEGIN
job_action END; This is done to declare some internal Scheduler
variables.

For a stored procedure, the action is the name of the stored
procedure. You have to specify the schema if the procedure resides in
another schema than the job.

For an executable, the action is the name of the external
executable, including the full path name and any command-line arguments.

If program_action is not specified, an error is
generated

If it is an anonymous block, special Scheduler metadata may be
accessed using the following variable names: job_name,
job_owner, job_start, window_start,
window_end. For more information on these, see the
information regarding define_metadata_argument.

number_of_arguments

This attribute specifies the number of arguments the program
takes. If this parameter is not specified then the default will be 0. A
program can have a maximum of 255 number of arguments.

If the program_type is PLSQL_BLOCK,
this field is ignored.

enabled

This flag specifies whether the program should be created
enabled or not. If the flag is set to TRUE, then validity
checks will be made and the program will be created ENABLED
should all the checks be successful. By default, this flag is set to
FALSE, which means that the program is not created enabled.
You can also call the ENABLE procedure to enable the program
before it can be used.

comments

A comment about the program. By default, this attribute is
NULL.

Usage
Notes

To create a program in his own schema, a user needs the
CREATE JOB privilege. A user with the
CREATE ANY JOB privilege can create a
program in any schema. A program is created in a disabled state by default
(unless the enabled field is set to TRUE). It cannot be executed by
a job until it is enabled.

For other users to use your programs, they must have
EXECUTE privileges, therefore once a program has been created, you
have to grant EXECUTE privilege on it.

CREATE_SCHEDULE Procedure

This procedure creates a schedule.

Syntax

DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
repeat_interval IN VARCHAR2,
end_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-8  CREATE_SCHEDULE
Procedure Parameters

Parameter

Description

schedule_name

This attribute specifies a unique identifier for the schedule.
The name has to be unique in the SQL namespace. For example, a schedule
cannot have the same name as a table i a schema. If no name is specified,
then an error occurs.

start_date

This attribute specifies the first date on which this schedule
becomes valid. For a repeating schedule, the value for
start_date is a reference date. In this case, the start of
the schedule is not the start_date. It depends on the repeat
interval specified. start_date is used to determine the first
instance of the schedule.

If start_date is specified in the past and no
value for repeat_interval is specified, the schedule is
invalid. For a repeating job or window, start_date can be
derived from the repeat_interval, if it is not
specified.

repeat_interval

This attribute specifies how often the schedule should repeat.
It is expressed using a calendar expression. See Table 83-9
for further information. PL/SQL expressions are not allowed as repeat
intervals for named schedules.

end_date

The date after which jobs will not run and windows will not
open.

A non-repeating schedule that has no end_date
will be valid forever.

end_date has to be after the
start_date. If this is not the case, then an error will be
generated when the schedule is created.

comments

This attribute specifies an optional comment about the
schedule. By default, this attribute is
NULL.

Syntax for
repeat_interval

Calendaring
Syntax

The calendaring syntax is as follows:

repeat_interval = frequency_clause
[";" interval_clause] [";" bymonth_clause] [";" byweekno_clause]
[";" byyearday_clause] [";" bymonthday_clause] [";" byday_clause]
[";" byhour_clause] [";" byminute_clause] [";" bysecond_clause]

frequency_clause = "FREQ" "=" frequency
frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" |
"HOURLY" | "MINUTELY" | "SECONDLY"
interval_clause = "INTERVAL" "=" intervalnum
intervalnum = 1 through 99
bymonth_clause = "BYMONTH" "=" monthlist
monthlist = monthday ( "," monthday) *
month = numeric_month | char_month
numeric_month = 1 | 2 | 3 ... 12
char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" |
"JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC"
byweekno_clause = "BYWEEKNO" "=" weeknumber_list
weeknumber_list = weekday ( "," weeknumber)*
week = [minus] weekno
minus = "-"
weekno = 1 through 53
byyearday_clause = "BYYEARDAY" "=" yearday_list
yearday_list = yearday ( "," yearday)*
yearday = [minus] yeardaynum
yeardaynum = 1 through 366
bymonthday_clause = "BYMONTHDAY" "=" monthday_list
monthday_list = monthday ( "," monthday) *
monthday = [minus] monthdaynum
monthdaynum = 1 through 31
byday_clause = "BYDAY" "=" byday_list
byday_list = byday ( "," byday)*
byday = [weekdaynum] day
weekdaynum = [minus] daynum
daynum = 1 through 53 /* if frequency is yearly */
daynum = 1 through 5 /* if frequency is monthly */
day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"
byhour_clause = "BYHOUR" "=" hour_list
hour_list = hour ( "," hour)*
hour = 0 through 23
byminute_clause = "BYMINUTE" "=" minute_list
minute_list = minute ( "," minute)*
minute = 0 through 59
bysecond_clause = "BYSECOND" "=" second_list
second_list = second ( "," second)*
second = 0 through 59

In calendaring syntax, * means 0 or more.

Table 83-9  Values for
repeat_interval

Name

Description

freq

This specifies the type of recurrence. It must be specified.
The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY,
and SECONDLY.

interval

This specifies a positive integer representing how often the
recurrence repeats. The default is 1, which means every second for
secondly, every day for daily, and so on. The maximum value is
999.

bymonth

This specifies which month or months you want the job to
execute in. You can use numbers such as 1 for January and 3 for March, as
well as three-letter abbreviations such as FEB for February and JUL for
July.

byweekno

This specifies the week of the year as a number. It follows
ISO-8601, which defines the week as starting with Monday and ending with
Sunday; and the first week of a year as the first week, which is mostly
within the Gregorian year. That last definition is equivalent to the
following two variants: the week that contains the first Thursday of the
Gregorian year; and the week containing January 4th.

The ISO-8601 week numbers are integers from 1 to 52 or 53;
parts of week 1 may be in the previous calendar year; parts of week 52 may
be in the following calendar year; and if a year has a week 53, parts of
it must be in the following calendar year.

As an example, in the year 1998 the ISO week 1 began on Monday
December 29th, 1997; and the last ISO week (week 53) ended on Sunday
January 3rd, 1999. So December 29th, 1997, is in the ISO week 1998-01; and
January 1st, 1999, is in the ISO week 1998-53.

byweekno is only valid for YEARLY.

Examples of invalid specifications are "FREQ=YEARLY;
BYWEEKNO=1; BYMONTH=12" and
"FREQ=YEARLY;BYWEEKNO=53;BYMONTH=1".

byyearday

This specifies the day of the year as a number. Valid values
are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for
February, and 10 for March). 69 evaluates to March 10 for non-leap years
and March 9 in leap years. -2 will always evaluate to December 30th
independent of whether it is a leap year.

bymonthday

This specifies the day of the month as a number. Valid values
are 1 to 31. An example is 10, which means the 10th day of the selected
month. You can use the minus sign (-) to count backward from the last day,
so, for example, BYMONTHDAY=-1 means the last day of the
month and BYMONTHDAY=-2 means the next to last day of the
month.

byday

This specifies the day of the week from Monday to Sunday in
the form MON, TUE, and so on. Using numbers, you can specify the 26th
Friday of the year, if using a YEARLY frequency, or the 4th THU of the
month, using a MONTHLY frequency. Using the minus sign, you can say the
second to last Friday of the month. For example, -1 FRI is
the last Friday of the month.

byhour

This specifies the hour on which the job is to run. Valid
values are 0 to 23. As an example, 10 means 10AM.

byminute

This specifies the minute on which the job is to run. Valid
values are 0 to 59. As an example, 45 means 45 minutes past the chosen
hour.

bysecond

This specifies the second on which the job is to run. Valid
values are 0 to 59. As an example, 30 means 30 seconds past the chosen
minute.

Usage
Notes

This procedure requires the CREATE JOB
privilege to create a schedule in one's own schema or the CREATE
ANY JOB privilege to create a schedule in someone
else's schema by specifying schema.schedule_name. Once a schedule
has been created, it can be used by other users. The schedule is created with
access to PUBLIC. Therefore, there is no need to explicitly grant
access to the schedule.

repeat_interval

When using a calendaring expression, consider the following
rules:

The calendar string must start
with the frequency clause. All other clauses are optional and can be put in
any order.

All clauses are separated by a
semi-colon and each clause can be present at most once.

Spaces are allowed between syntax
elements and the strings are case insensitive.

The list of values for a specific
BY clause do not need to be ordered.

When not enough BY
clauses are present to determine what the next date is, this information is
retrieved from the start date. For example, "FREQ=YEARLY" with a
start date of 02/15/2003 becomes "FREQ=YEARLY;BYMONTH=FEB;
BYMONTHDAY=15", which means every year on the 15th of February.

"FREQ=YEARLY;BYMONTH=JAN,JUL" with start date
01/21/2003 becomes "FREQ=YEARLY;BYMONTH=JAN,JUL;BYMONTHDAY=21",
which means every year on January 21 and July 21.

The byweekno clause
is only allowed if the frequency is YEARLY. It cannot be used
with other frequencies. When it is present, it will return all days in that
week number. If you want to limit it to specific days within the week, you
have to add a BYDAY clause. For example,
"FREQ=YEARLY;BYWEEKNO=2" with a start date of 01/01/2003 will
return:
01/06/2003, 01/07/2003, 01/08/2003, 01/09/2003, 01/10/2003, 01/11/2003,
01/12/2003, 01/05/2004, 01/06/2004, 01/07/2004, .... and so on.

Note that when the byweekno clause is used, it is
possible that the dates returned are from a year other than the current year.
For example, if returning dates for the year 2004 and the calendar string is
"FREQ=YEARLY;BYWEEKNO=1,53" for the specified week numbers in
2004, it will return the dates:

12/29/03, 12/30/03, 12/31/03, 01/01/04, 01/02/04, 01/03/04, 01/04/04,
12/27/04, 12/28/04, 12/29/04, 12/30/04, 12/31/04, 01/01/05, 01/02/05

For those BY clauses
that do not have a consistent range of values, you can count backward by
putting a "-" in front of the numeric value. For example, specifying
BYMONTHDAY=31 will not give you the last day of every month,
because not every month has 31 days. Instead, BYMONTHDAY=-1 will
give you the last day of the month.

This is not supported for BY clauses that are fixed
in size. In other words, bymonth, byhour,
byminute, and bysecond are not supported.

The basic values for the
byday clause are the days of the week. When the frequency is
YEARLY, or MONTHLY, you are allowed to specify a
positive or negative number in front of each day of the week. In the case of
YEARLY, BYDAY=40MON, indicates the 40th Monday of
the year. In the case of MONTHLY, BYDAY=-2SAT, indicates the
second to last Saturday of the month.

Note that positive or negative numbers in front of the weekdays
are not supported for other frequencies and that in the case of yearly, the
number ranges from -53 ... -1, 1 ... 53, whereas for the monthly frequency it
is limited to -5 ... -1, 1... 5.

If no number is present in front of the weekday it specifies,
every occurrence of that weekday in the specified frequency.

The first day of the week is
Monday.

The calendaring syntax does not
allow you to specify a time zone. Instead the scheduler retrieves the time
zone from the start_date argument. If jobs must follow daylight
savings adjustments you must make sure that you specify a region name for the
time zone of the start_date. For example specifying the
start_date time zone as 'US/Eastern' in New York
will make sure that daylight saving adjustments are automatically applied. If
instead the time zone of the start_date is set to an absolute
offset, such as '-5:00', daylight savings adjustments are not
followed and your job execution will be off by an hour half of the year.

When start_date is
NULL, the scheduler will determine the time zone for the repeat
interval as follows:

It will check whether the
session time zone is a region name. The session time zone can be set by
either:

- issuing an
ALTER SESSION statement, for example:
SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';

- or by setting the
ORA_SDTZ environment variable.

If the session time zone is
an absolute offset instead of a region name, the scheduler will use the
value of the DEFAULT_TIMEZONE scheduler attribute. For more
information see the SET_SCHEDULER_ATTRIBUTE
Procedure.

If the DEFAULT_TIMEZONE
attribute is NULL, the scheduler will use the time zone of systimestamp when
the job or window is enabled.

CREATE_WINDOW Procedures

This procedure creates a Scheduler window consisting of a recurring
time window and an associated resource plan.

The procedure is overloaded. The different functionality of each
form of syntax is presented along with the syntax declaration.

Syntax

Creates a window using a named schedule object:

DBMS_SCHEDULER.CREATE_WINDOW (
window_name IN VARCHAR2,
resource_plan IN VARCHAR2,
schedule_name IN VARCHAR2,
duration IN INTERVAL DAY TO SECOND,
window_priority IN VARCHAR2 DEFAULT 'LOW',
comments IN VARCHAR2 DEFAULT NULL);

Creates a window using an inlined schedule:

DBMS_SCHEDULER.CREATE_WINDOW (
window_name IN VARCHAR2,
resource_plan IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
duration IN INTERVAL DAY TO SECOND,
window_priority IN VARCHAR2 DEFAULT 'LOW',
comments IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-10  CREATE_WINDOW
Procedure Parameters

Parameter

Description

window_name

This attribute uniquely identifies the window, and is of the
form [SYS.]name. The name has to be unique in the SQL
namespace, thus no other database object should have the same
name.

resource_plan

This attribute specifies the resource plan. When creating a
window, a resource plan enables you to specify how resources will be
allocated among the various job classes during this window by associating
a resource plan with it. When the window opens, the system switches to the
specified resource plan. When the window closes, the system switches to
the appropriate resource plan. In most cases, this is the resource plan
that was in effect before the window opened, but it can also be the
resource plan of yet another window.

Besides using windows to automatically switch resource plans,
you can also manually activate a plan by using the ALTER
SYSTEM SET RESOURCE_MANAGER_PLAN
statement. For certain emergency scenarios, you do not want the Scheduler
to switch resource plans. When the preceding statement is used with the
force option, the Scheduler will not be allowed to switch
resource plans. It will still attempt to do so, but a runtime error will
be written to the window log.

Only one resource plan can be associated with a window. If no
resource plan is associated with the window during its creation, the
resource plan that is in effect when the window opens will stay in effect
for the duration of the window.

If the window is open and the resource plan is dropped, then
the resource allocation for the duration of the window is not affected.

A runtime error will occur when a window is about to open and
the resource plan associated with this window has been dropped.

start_date

This attribute specifies the first date on which this window
is scheduled to open. If the value for start_date specified
is in the past or is not specified, the window opens as soon as it is
created.

For repeating windows that use a calendaring expression to
specify the repeat interval, the value for start_date is a
reference date. The first time the window opens depends on the repeat
interval specified and the value for start_date.

duration

This attribute specifies how long the window will be open for.
For example, 'interval '5' hour' for five hours. There is no default value
for this attribute. Therefore, if none is specified when creating the
window, an error occurs. The duration is of type interval day to seconds
and ranges from one minute to 99 days.

schedule_name

The name of the schedule associated with the window.

repeat_interval

This attribute specifies how often the window should repeat.
It is expressed using the Scheduler's calendaring syntax.

A PL/SQL expression cannot be used to specify the repeat
interval for a window.

The expression specified is evaluated to determine the next
time the window should open. If no repeat_interval is specified, the
window will open only once at the specified start date.

end_date

This attribute specifies the date after which the window will
no longer open. When the value for end_date is reached, the
window is disabled. In the *_SCHEDULER_WINDOWS views, the
enabled flag of the window will be set to FALSE.

A non-repeating window that has no value for end_date opens
only once for the duration of the window. For a repeating window, if no
end_date is specified then the window will keep repeating
forever.

The end_date has to be after the start_date. If
this is not the case, then an error is generated when the window is
created.

window_priority

This attribute is only relevant when two windows overlap.
Because only one window can be in effect at one time, the window priority
will be used to determine which window will be opened. The two possible
values for this attribute are high and low. A high priority window has
precedence over a low priority window, which implies that the low priority
window does not open if it overlaps with a high priority window. By
default, a window is created with a priority of low.

comments

This attribute specifies an optional comment about the window.
By default, this attribute is NULL.

Usage
Notes

Creating a window requires the MANAGE
SCHEDULER privilege. Windows always reside in the SYS
schema.

CREATE_WINDOW_GROUP Procedure

This procedure creates a new window group.

Syntax

DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
group_name IN VARCHAR2,
window_list IN VARCHAR2 DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-11  CREATE_WINDOW_GROUP
Procedure Parameters

Parameter

Description

group_name

The name of the window group.

window_list

A list of the windows assigned to the window group. If a
window that does not exist is specified then, an error is generated and
the window group is not created.

Windows can also be added using the
ADD_WINDOW_GROUP_MEMBER procedure. A window group cannot be a
member of another window group. Can be NULL.

comments

A comment about the window group.

Usage
Notes

Creating a window group requires the MANAGE
SCHEDULER privilege. Window groups reside in the SYS
schema. Window groups, like windows, are created with access to
PUBLIC, therefore, no privileges are required to access window
groups.

A window group cannot contain another window group

DEFINE_ANYDATA_ARGUMENT Procedure

This procedure defines a program argument whose value is of a
complex type and must be encapsulated within an AnyData
object.

Syntax

DBMS_SCHEDULER.DEFINE_ANYDATA_ARGUMENT (
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL,
argument_type IN VARCHAR2,
default_value IN SYS.ANYDATA,
out_argument IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-12
DEFINE_ANYDATA_ARGUMENT Procedure Parameters

Parameter

Description

program_name

The name of the program to be altered. A program with this
name must exist.

argument_name

The name of the argument being set. It is optional, but must
be unique if it is specified, so no other argument must exist with this
name for this program if it is non-NULL.

argument_position

This specifies the position of the argument when it is being
passed to the executable, and is required. Argument numbers go from one up
to the number_of_arguments specified for the program. This
must be unique, so it will replace any argument already defined at this
position.

argument_type

The type of argument being defined. This is not verified or
used by the Scheduler. It is only used by the user of the program when
deciding what value to assign to the argument.

default_value

The default value to be assigned to the argument encapsulated
within an AnyData object. This is optional.

out_argument

This parameter is reserved for future use. It must be set to
FALSE.

Usage
Notes

Defining a program argument requires that you be the owner of the
program or have ALTER privileges on that program. You can also
define a program argument if you have the CREATE ANY
JOB privilege.

DEFINE_METADATA_ARGUMENT Procedure

This procedure defines a special metadata argument for the program.
You can retrieve specific Scheduler metadata through this argument. You cannot
set values for jobs using this argument. Valid metadata attributes are:
job_name, job_owner, job_start,
window_start, and window_end.

Syntax

DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (
program_name IN VARCHAR2,
metadata_attribute IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-13
DEFINE_METADATA_ARGUMENT Procedure
Parameters

Parameter

Description

program_name

The name of the program to be altered.

metadata_attribute

The metadata to be retrieved.

argument_position

Specifies the position of the argument when it is being passed
to the executable, and is required. This cannot be greater than the
number_of_arguments specified for the program. This must be
unique, so it will replace any argument already defined at this
position.

argument_name

The name of the argument being set. It is optional. This must
be unique if it is specified, so no other argument must exist with this
name for this program if it is
non-NULL.

Usage
Notes

Defining a program argument requires that you be the owner of the
program or have ALTER privileges on that program. You can also
define a program argument if you have the CREATE ANY
JOB privilege.

DEFINE_PROGRAM_ARGUMENT Procedure

This procedure defines program arguments. This does not affect
whether a program is enabled or not. Defining a program argument can be used to
assign a default value or a name to the argument.

This procedure is overloaded. The different functionality of each
form of syntax is presented along with the syntax declaration.

Syntax

Defines a program argument with a default value:

PROCEDURE define_program_argument(
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL,
argument_type IN VARCHAR2,
out_argument IN BOOLEAN DEFAULT FALSE);

Defines a program argument without a default value:

PROCEDURE define_anydata_argument(
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL,
argument_type IN VARCHAR2,
default_value IN SYS.ANYDATA,
out_argument IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-14
DEFINE_PROGRAM_ARGUMENT Procedure Parameters

Parameter

Description

program_name

The name of the program to be altered. A program with this
name must exist.

argument_name

The name of the argument being set. It is optional, but must
be unique if it is specified, so no other argument must exist with this
name for this program if it is non-NULL.

argument_position

This specifies the position of the argument when it is being
passed to the executable, and is required. Argument numbers go from one up
to the number_of_arguments specified for the program. This
must be unique so it will replace any argument already defined at this
position.

argument_type

The type of argument being defined. This is not verified or
used by the Scheduler. It is only used by the user of the program when
deciding what value to assign to the argument.

default_value

The default value to be assigned to the argument if none is
specified by the job.

out_argument

This parameter is reserved for future use. It must be set to
FALSE.

Usage
Notes

All program arguments from 1 to the number_of_arguments
value must be defined before a program can be enabled.

Defining a program argument requires that you be the owner of the
program or have ALTER privileges on that program. You can also
define a program argument if you have the CREATE ANY
JOB privilege.

DISABLE
Procedure

This procedure disables a program, job, window, or window
group.

Syntax

DBMS_SCHEDULER.DISABLE (
name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-15  DISABLE Procedure
Parameters

Parameter

Description

name

The name of the object being disabled. Can be a
comma-delimited list.

If a job class name is specified, then all the jobs in the job
class are disabled. The job class is not disabled.

If a window group name is specified, then the window group
will be disabled, but the windows that are members of the window group,
will not be disabled.

force

Whether to ignore dependencies. See preceding notes for more
information.

Usage
Notes

Disabling an object that is already disabled does not generate an
error. Because the DISABLE procedure is used for several Scheduler
objects, when disabling windows and window groups, they must be preceded by
SYS.

The purpose of the force option is to point out
dependencies. No dependent objects are altered.

To run DISABLE for a window or window group, you must
have the MANAGE SCHEDULER privilege. Otherwise, you
must be the owner of the object being disabled or have ALTER
privileges on that object or have the CREATE ANY
JOB privilege.

Job

Disabling a job means that, although the metadata of the job is
there, it should not run and the job coordinator will not pick up these jobs for
processing. When a job is disabled, its state in the job queue is
changed to disabled.

If force is set to FALSE and the job is
currently running, an error is returned.

If force is set to TRUE, the job is
disabled, but the currently running instance is allowed to finish.

Program

When a program is disabled, the status is changed to disabled. A
disabled program implies that, although the metadata is still there, jobs that
point to this program cannot run.

If force is set to FALSE, the program must
be unreferenced by any job otherwise an error will occur.

If force is set to TRUE, those jobs that
point to the program will not be disabled, however, they will fail at runtime
because their program will not be valid.

Running jobs that point to the program are not affected by the
DISABLE call, and are allowed to continue

Any argument that pertains to the program will not be affected when
the program is disabled.

Window

This means that the window will not open, however, the metadata of
the window is still there, so it can be reenabled.

If force is set to FALSE, the window must
not be open or referenced by any job otherwise an error will occur.

If force is set to TRUE, disabling a
window that is open will succeed but the window will not be closed. It will
prevent the window from opening in the future until it is re-enabled.

When the window is disabled, those jobs that have the window as
their schedule will not be disabled.

Window
Group

When a window group is disabled, jobs, other than a running job,
that has the window group as its schedule will not run even if the member
windows open. However, if the job had one of the window group members as its
schedule, it would still run.

The metadata of the window group is still there, so it can be
reenabled. Note that the members of the window group will still open.

If force is set to FALSE, the window group
must not have any members that are open or referenced by any job otherwise an
error will occur.

If force is set to TRUE:

The window group is disabled and
the open window will be not closed or disabled. It will be allowed to continue
to its end.

The window group is disabled but
those jobs that have the window group as their schedule will not be
disabled.

DROP_JOB
Procedure

This procedure drops a job or all jobs in a job class. It results in
the job being removed from the job queue, its metadata being removed, and no
longer being visible in the *_SCHEDULER_JOBS views. Therefore, no
more runs of the job will be executed. Dropping a job also drops all argument
values set for that job.

Syntax

DBMS_SCHEDULER.DROP_JOB (
job_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-16  DROP_JOB Procedure
Parameters

Parameter

Description

job_name

The name of a job or job class. Can be a comma-delimited list.
For a job class, the SYS schema should be specified.

If the name of a job class is specified, the jobs that belong
to that job class are dropped, but the job class itself is not
dropped.

force

If force is set to FALSE, and an
instance of the job is running at the time of the call, the call results
in an error.

If force is set to TRUE, the
Scheduler attempts to first stop (issues the STOP_JOB call)
the running job instance and then drop the job.

DROP_JOB_CLASS Procedure

This procedure drops a job class. Dropping a job class means that
all the metadata about the job class is removed from the database.

Syntax

DBMS_SCHEDULER.DROP_JOB_CLASS (
job_class_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-17 vDROP_JOB_CLASS Procedure
Parameters

Parameter

Description

job_class_name

The name of the job class. Can be a comma-delimited
list.

force

If force is set to FALSE, a class
must be unreferenced by any jobs to be dropped otherwise an error will
occur.

If force is set to TRUE, jobs
belonging to the class are disabled and their class is set to the default
class. Only if this is successful will the class be dropped.

Running jobs that belong to the job class are not
affected.

Usage
Notes

Dropping a class requires the MANAGE
SCHEDULER system privilege.

DROP_JOB requires that you be the owner of the job or
have ALTER privileges on that job. You can also drop a job if you
have the CREATE ANY JOB privilege.

DROP_PROGRAM Procedure

This procedure drops a program. Any arguments that pertain to the
program are also dropped when the program is dropped.

Syntax

DBMS_SCHEDULER.DROP_PROGRAM (
program_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-18  DROP_PROGRAM Procedure
Parameters

Parameter

Description

program_name

The name of the program to be dropped. Can be a
comma-delimited list.

force

If force is set to FALSE, the
program must be unreferenced by any job otherwise an error will occur.

If force is set to TRUE, all jobs
referencing the program are disabled before dropping the program.

Running jobs that point to the program are not affected by the
DROP_PROGRAM call, and are allowed to
continue.

Usage
Notes

Dropping a program requires that you be the owner of the program or
have ALTER privileges on that program. You can also drop a program
if you have the CREATE ANY JOB
privilege.

DROP_PROGRAM_ARGUMENT Procedures

This procedure drops a program argument. An argument can be
specified by either name (if one has been given) or position.

The procedure is overloaded. The different functionality of each
form of syntax is presented along with the syntax declaration.

Syntax

Drops a program argument either by position:

DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER);

Drops a program argument either by name:

DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
program_name IN VARCHAR2,
argument_name IN VARCHAR2);

Parameters

Table 83-19  DROP_PROGRAM_ARGUMENT
Procedure Parameters

Parameter

Description

program_name

The name of the program to be altered. A program with this
name must exist.

argument_name

The name of the argument being dropped.

argument_position

The position of the argument to be
dropped.

Usage
Notes

Dropping a program argument requires that you be the owner of the
program or have ALTER privileges on that program. You can also
define a program argument if you have the CREATE ANY
JOB privilege.

DROP_SCHEDULE Procedure

This procedure drops a schedule.

Syntax

DBMS_SCHEDULER.DROP_SCHEDULE (
schedule_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-20  DROP_SCHEDULE
Procedure Parameters

Parameter

Description

schedule_name

The name of the schedule. Can be a comma-delimited
list.

force

If force is set to FALSE, the
schedule must be unreferenced by any job or window otherwise an error will
occur.

If force is set to TRUE, any jobs or
windows that use this schedule will be disabled before the schedule is
dropped

Running jobs and open windows that point to the schedule are
not affected.

Usage
Notes

You must be the owner of the schedule being dropped or have
ALTER privileges for the schedule or the CREATE
ANY JOB privilege.

DROP_WINDOW Procedure

This procedure drops a window. All metadata about the window is
removed from the database. All references to the window are removed from window
groups.

Syntax

DBMS_SCHEDULER.DROP_WINDOW (
window_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-21  DROP_WINDOW Procedure
Parameters

Parameter

Description

window_name

The name of the window. Can be a comma-delimited
list.

force

If force is set to FALSE, the window
must be not be open or referenced by any job otherwise an error will
occur.

If force is set to TRUE, the window
will be dropped and those jobs that have the window as their schedule will
be disabled. However, jobs that have a window group of which the dropped
window was a member as their schedule will not be disabled. If the window
is open then, the Scheduler attempts to first close the window and then
drop it. When the window is closed, normal close window rules apply.

Running jobs that have the window as their schedule will be
allowed to continue, unless the stop_on_window_close flag was
set to TRUE for the job. If this is the case, the job will be
stopped when the window is dropped.

Usage
Notes

Dropping a window requires the MANAGE
SCHEDULER privilege.

DROP_WINDOW_GROUP Procedure

This procedure drops a window group but not the windows that are
members of this window group.

Syntax

DBMS_SCHEDULER.DROP_WINDOW_GROUP (
group_name IN VARCHAR2
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-22  DROP_WINDOW_GROUP
Procedure Parameters

Parameter

Description

group_name

The name of the window group.

force

If force is set to FALSE, the window
group must be unreferenced by any job otherwise an error will occur.

If force is set to TRUE, the window
group will be dropped and those jobs that have the window group as their
schedule will be disabled. Running jobs that have the window group as
their schedule are allowed to continue, even if the
stop_on_window_close flag was set to TRUE when
for the job.

If a member of the window group that is being dropped is open,
the window group can still be dropped.

Usage
Notes

If you want to drop all the windows that are members of this group
but not the window group itself, you can use the DROP_WINDOW
procedure and provide name of the window group to the call.

To drop a window group, you must have the MANAGE
SCHEDULER privilege.

ENABLE
Procedure

This procedure enables a program, job, window, or window group. When
an object is enabled, the enabled flag is set to TRUE. By default,
jobs and programs are created disabled and windows and window groups are created
enabled.

Validity checks are performed before enabling an object. If the
check fails, the object is not enabled, and an appropriate error is returned.
This procedure does not return an error if the object was already enabled.

Syntax

DBMS_SCHEDULER.ENABLE (
name IN VARCHAR2);

Parameters

Table 83-23  ENABLE Procedure
Parameters

Parameter

Description

name

The name of the Scheduler object being enabled. Can be a
comma-delimited list.

If a job class name is specified, then all the jobs in the job
class are enabled.

If a window group name is specified, then the window group
will be enabled, but the windows that are members of the window group,
will not be enabled.

Usage
Notes

Because the ENABLE procedure is used for several
Scheduler objects, when enabling windows or window groups, they must be preceded
by SYS.

To run ENABLE for a window or window group, you must
have the MANAGE SCHEDULER privilege. Otherwise, you
must be the owner of the object being enabled or have ALTER
privileges on that object or have the CREATE ANY
JOB privilege.

EVALUATE_CALENDAR_STRING Procedure

You can define repeat intervals of jobs, windows or schedules using
the Scheduler's calendar syntax. This procedure evaluates the calendar string
and tells you what the next execution date of a job or window will be. This is
very useful for testing the correct definition of the calendar string without
having to actually schedule the job or window.

This procedure can also be used to get multiple steps of the repeat
interval by passing the next_run_date returned by one invocation as
the return_date_after argument of the next invocation of this
procedure.

Syntax

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE,
return_date_after IN TIMESTAMP WITH TIME ZONE,
next_run_date OUT TIMESTAMP WITH TIME ZONE);

Parameters

Table 83-24
EVALUATE_CALENDAR_STRING Procedure
Parameters

Parameter

Description

calendar_string

The calendar string to be evaluated.

start_date

The date after which the repeat interval becomes valid. It can
also be used to fill in specific items that are missing from the calendar
string. Can optionally be NULL.

return_date_after

With the start_date and the calendar string, the
Scheduler has sufficient information to determine all valid execution
dates. By setting this argument, the Scheduler knows which one of all
possible matches to return. When a NULL value is passed for
this argument, the Scheduler automatically fills in
systimestamp as its value.

next_run_date

The first timestamp that matches the calendar string and start
date that occurs after the value passed in for the
return_date_after argument.

Examples

The following code fragment can be used to determine the next five
dates a job will run given a specific calendar string.

SET SERVEROUTPUT ON;
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date :=
to_timestamp_tz('01-JAN-2003 10:00:00','DD-MON-YYYY HH24:MI:SS');
return_date_after := start_date;
FOR i IN 1..5 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'FREQ=DAILY;BYHOUR=9;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI',
start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
/

next_run_date: 02-JAN-03 09.30.00.000000 AM
next_run_date: 03-JAN-03 09.30.00.000000 AM
next_run_date: 06-JAN-03 09.30.00.000000 AM
next_run_date: 07-JAN-03 09.30.00.000000 AM
next_run_date: 08-JAN-03 09.30.00.000000 AM

PL/SQL procedure successfully completed.

GENERATE_JOB_NAME Function

This function returns a unique name for a job. The name will be of
the form {prefix}N where N is a number from a
sequence. If no prefix is specified, the generated name will, by default, be
JOB$_1, JOB$_2, JOB$_3, and so on. If
'SCOTT' is specified as the prefix, the name will be
SCOTT1, SCOTT2, and so on.

Syntax

DBMS_SCHEDULER.GENERATE_JOB_NAME (
prefix IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2;

Parameters

Table 83-25  GENERATE_JOB_NAME
Procedure Parameters

Parameter

Description

prefix

The name of the prefix being
returned.

s

Usage
Notes

If the prefix is explicitly set to NULL, the name will
be just the sequence number. In order to successfully use such numeric names,
they must be surrounded by double quotes throughout the
DBMS_SCHEDULER calls. A prefix cannot be longer than 18 characters
and cannot end with a digit.

Note that, even though the GENERATE_JOB_NAME function
will never return the same job name twice, there is a small chance that the
returned name happens to match an already existing database object.

GET_ATTRIBUTE Procedure

This procedure retrieves the value of an attribute of a Scheduler
object. It is overloaded to output values of the following types:
VARCHAR2, TIMESTAMP WITH
TIMEZONE, BOOLEAN, PLS_INTEGER, and
INTERVAL DAY TO
SECOND.

Syntax

DBMS_SCHEDULER.GET_ATTRIBUTE (
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT [VARCHAR2, TIMESTAMP WITH TIMEZONE,
PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND]);

Parameters

Table 83-26  GET_ATTRIBUTE
Procedure Parameters

Parameter

Description

name

The name of the object.

attribute

The attribute being retrieved.

value

The existing value of the attribute.

Usage
Notes

To run GET_ATTRIBUTE for a job class, you must have the
MANAGE SCHEDULER privilege or have
EXECUTE privileges on the class. For a schedule, window, or a
window group, no privileges are necessary. Otherwise, you must be the owner of
the object or have ALTER or EXECUTE privileges on that
object or have the CREATE ANY JOB privilege.

GET_SCHEDULER_ATTRIBUTE Procedure

This procedure retrieves the value of a Scheduler attribute. The
attributes you can retrieve are max_job_slave_processes,
log_history, and current_open_window. The Scheduler
attribute current_open_window can only be retrieved (it can never be set) and it
will contain the name of the currently active window, if any.

Syntax

DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE (
attribute IN VARCHAR2,
value OUT VARCHAR2);

Parameters

Table 83-27
GET_SCHEDULER_ATTRIBUTE Procedure Parameters

Parameter

Description

attribute

The name of the Scheduler attribute.

value

The existing value of the attribute.

Usage
Notes

To run GET_SCHEDULER_ATTRIBUTE, you must have the
MANAGE SCHEDULER privilege.

OPEN_WINDOW Procedure

This procedure opens a window independent of its schedule. This
window will open and the resource plan associated with it, will take effect
immediately for the duration specified or for the normal duration of the window
if no duration is given. Only an enabled window can be manually opened.

Syntax

DBMS_SCHEDULER.OPEN_WINDOW (
window_name IN VARCHAR2,
duration IN INTERVAL DAY TO SECOND,
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-28  OPEN_WINDOW Procedure
Parameters

Parameter

Description

window_name

The name of the window.

duration

The duration of the window. It is of type interval day to
second. If it is not specified, then the window will be opened for the
regular duration as specified in the window metadata.

force

If force is set to FALSE, opening an
already open window, will generate an error.

If force is set to TRUE:

You can open a window that is already open. The window stays
open for the duration specified in the call, from the time the
OPEN_WINDOW command was issued. Consider an example to
illustrate this. window1 was created with a duration of four
hours. It has how been open for two hours. If at this point you reopen
window1 using the OPEN_WINDOW call and do not
specify a duration, then window1 will be open for another
four hours because it was created with that duration. If you specified a
duration of 30 minutes, the window will close in 30 minutes.

The Scheduler automatically closes any window that is open at
that time, even if it has a higher priority. For the duration of this
manually opened window, the Scheduler does not open any other scheduled
windows even if they have a higher priority.

Usage
Notes

If there are jobs running when the window opens, the resources
allocated to them might change due to the switch in resource plan.

Opening a window manually has no impact on regular scheduled runs of
the window. The next open time of the window is not updated, and will be as
determined by the regular scheduled opening.

When a window that was manually opened closes, the rules about
overlapping windows are applied to determine which other window should be opened
at that time if any at all.

A window can fail to open if the resource plan has been manually
switched using the ALTER SYSTEM statement with the
force option.

Opening a window requires the MANAGE
SCHEDULER privilege for that window.

PURGE_LOG
Procedure

By default, the Scheduler automatically purges all rows in the job
log and window log that are older than 30 days. The PURGE_LOG
procedure is used to purge additional rows from the job and window log.

Syntax

DBMS_SCHEDULER.PURGE_LOG (
log_history IN PLS_INTEGER DEFAULT 0,
which_log IN VARCHAR2 DEFAULT 'JOB_AND_WINDOW_LOG',
job_name IN VARCHAR2 DEFAULT NULL);

Parameters

Table 83-29  PURGE_LOG Procedure
Parameters

Parameter

Description

log_history

This specifies how much history (in days) to keep. The valid
range is 0 - 999. If set to 0, no history is kept.

which_log

This specifies which type of log. Valid values for
which_log are job_log, window_log,
and job_and_window_log.

job_name

This specifies which job-specific entries must be purged from
the jog log. This can be a comma-delimited list of job names and job
classes. Whenever job_name has a value other than
NULL, the which_log argument implicitly includes
the job log.

Examples

The following will completely purge all rows from both the job log
and the window log:

DBMS_SCHEDULER.PURGE_LOG();

The following will purge all rows from the window log that are older
than 5 days:

DBMS_SCHEDULER.PURGE_LOG(5, 'window_log');

The following will purge all rows from the window log that are older
than 1 day and all rows from the job log that are related to jobs in
jobclass1 and are older than 1 day:

DBMS_SCHEDULER>PURGE_LOG(1, 'job_and_window_log', 'sys.jobclass1');

REMOVE_WINDOW_GROUP_MEMBER Procedure

This procedure removes one or more windows from an existing window
group.

Syntax

DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER (
group_name IN VARCHAR2,
window_list IN VARCHAR2);

Parameters

Table 83-30
REMOVE_WINDOW_GROUP_MEMBER Procedure Parameters

Parameter

Description

group_name

The name of the window group.

window_list

The name of the window or windows.

Usage
Notes

If any of the windows specified is either invalid, does not exist,
or is not a member of the given group, the call fails. Removing a window from a
group requires the MANAGE SCHEDULER privilege.

Dropping an open window from a window group has no impact on running
jobs that has the window as its schedule since the jobs would only be stopped
when a window closes.

RESET_JOB_ARGUMENT_VALUE Procedures

This procedure resets (clears) the value previously set to an
argument for a job.

RESET_JOB_ARGUMENT_VALUE is overloaded.

Syntax

Clears a previously set job argument value by argument position:

DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER);

Clears a previously set job argument value by argument name:

DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_name IN VARCHAR2);

Parameters

Table 83-31
RESET_JOB_ARGUMENT_VALUE Procedure Parameters

Parameter

Description

job_name

The name of the job being queried.

argument_position

The position of the program argument being altered.

argument_name

The name of the program argument being
altered.

Usage
Notes

If the corresponding program argument has no default value, the job
will be disabled. Resetting a program argument of a job belonging to another
user requires ALTER privileges on that job. Arguments can be
specified by position or by name.

RESET_JOB_ARGUMENT_VALUE requires that you be the owner
of the job or have ALTER privileges on that job. You can also set a
job argument value if you have the CREATE ANY
JOB privilege.

RUN_JOB
Procedure

This procedure runs a job immediately.

Syntax

DBMS_SCHEDULER.RUN_JOB (
job_name IN VARCHAR2,
use_current_session IN BOOLEAN DEFAULT TRUE);

Parameters

Table 83-32  RUN_JOB Procedure
Parameters

Parameter

Description

job_name

The name of the job being run.

use_current_session

This specifies whether the job run should occur in the same
session as the one that the procedure was invoked from.

When use_current_session is set to
TRUE:

-You can test a job and see any possible errors on the command
line.

-run_count, last_start_date,
last_run_duration, and failure_count are not
updated.

-RUN_JOB can be run in parallel with a regularly
scheduled job run.

When use_current_session is set to
FALSE:

-You need to check the job log to find error information.

-run_count, last_start_date,
last_run_duration, and failure_count are
updated.

-RUN_JOB fails if a regularly scheduled job is
running.

Usage
Notes

A job must be enabled for RUN_JOB to run. The job can
be run in two different modes. One is in the current user session. In this case,
the call to RUN_JOB will block until it has completed the job. Any
errors that occur during the execution of the job will be returned as errors to
the RUN_JOB procedure. The other option is to run the job
immediately like a regular job. In this case, RUN_JOB returns
immediately and the job will be picked up by the coordinator and passed on to a
job slave for execution. The scheduler views and logs must be queried for the
outcome of the job.

Multiple user sessions can use RUN_JOB in their
sessions simultaneously when use_current_session is set to
TRUE.

RUN_JOB requires that you be the owner of the job or
have ALTER privileges on that job. You can also run a job if you
have the CREATE ANY JOB privilege.

SET_ATTRIBUTE Procedure

This procedure changes an attribute of a Scheduler object. It is
overloaded to accept values of the following types: VARCHAR2,
TIMESTAMP WITH TIMEZONE,
BOOLEAN, PLS_INTEGER, and INTERVAL
DAY TO SECOND. To set an attribute to
NULL, the SET_ATTRIBUTE_NULL procedure should be used.
What attributes can be set depends on the object being altered. With the
exception of the object name, all object attributes can be changed.

SET_ATTRIBUTE is overloaded.

Syntax

DBMS_SCHEDULER.SET_ATTRIBUTE (
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN [VARCHAR2, TIMESTAMP WITH TIMEZONE,
PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND]);

Parameters

Table 83-33  SET_ATTRIBUTE
Procedure Parameters

Parameter

Description

name

The name of the object.

attribute

See Table 83-34,
Table 83-35,
Table 83-36,
Table 83-37,
Table 83-38,
and Table 83-38.

value

The new value being set for the attribute. This cannot be
NULL. To set an attribute value to NULL, use the
SET_ATTRIBUTE_NULL procedure.

Usage
Notes

If an object is altered and it was in the enabled state, the
Scheduler will first disable it, make the change and then re-enable it. If any
errors are encountered during the enable process, the object is not re-enabled
and an error is generated.

If an object is altered and it was in the disabled, it will remain
disabled after it is altered.

To run SET_ATTRIBUTE for a window, window group, or job
class, you must have the MANAGE SCHEDULER privilege.
Otherwise, you must be the owner of the object being altered or have
ALTER privileges on that object or have the CREATE
ANY JOB privilege.

Job

If there is a running instance of the job when the
SET_ATTRIBUTE call is made, it is not affected by the call. The
change is only seen in future runs of the job.

If any of the schedule attributes of a job are altered while the job
is running, the time of the next job run will be scheduled using the new
schedule attributes. Schedule attributes of a job include
schedule_name, start_date, end_date, and
repeat_interval.

If any of the program attributes of a job are altered while the job
is running, the new program attributes will take effect the next time the job
runs. Program attributes of a job include program_name,
job_action, job_type, and
number_of_arguments. This is also the case for job argument values
that have been set.

Granting ALTER on a job will let a user alter all
attributes of that job except its program attributes (program_name,
job_type, job_action, program_action, and
number_of_arguments) and will not allow a user to use a PL/SQL
expression to specify the schedule for a job.

We recommend you not to alter a job that was automatically created
for you by the database. Jobs that were created by the database have the column
SYSTEM set to TRUE in several views.

Program

If any currently running jobs use the program that is altered, they
will continue to run with the program definition prior to the alter. The job
will run with the new program definition the next time the job executes.

Schedule

If a schedule is altered, the change will not affect running jobs
and open windows that use this schedule. The change will only be in effect the
next time the jobs runs or the window opens.

Job Class

With the exception of the default job class, all job classes can be
altered. To alter a job class, you must have the MANAGE
SCHEDULER privilege.

When a job class is altered, running jobs that belong to the class
are not affected. The change only takes effect for jobs that have not started
running yet.

Window

When a window is altered, it does not affect an active window. The
changes only take effect the next time the window opens.

To change resource plans, you must first set the
RESOURCE_MANAGER_PLAN initialization parameter in the
init.ora file or issue an ALTER SYSTEM SET
RESOURCE_MANAGER_PLAN = my_plan statement before
the window opens.

Job Attribute
Values

Table 83-34
lists job attribute values.

Table 83-34  Job Attribute
Values

Name

Description

logging_level

This attribute specifies how much information is logged. The
three possible options are:

DBMS_SCHEDULER.LOGGING_OFF

No logging will be performed for any jobs in this class.

DBMS_SCHEDULER.LOGGING_RUNS

The Scheduler will write detailed information to the job log
for all runs of each job in this class.

DBMS_SCHEDULER.LOGGING_FULL

In addition to recording every run of a job, the Scheduler
will record all operations performed on all jobs in this class. In other
words, every time a job is created, enabled, disabled, altered, and so on
will be recorded in the log.

restartable

This attribute specifies whether a job can be restarted in
case of failure. By default, jobs are restartable and this attribute is
set to TRUE. Setting this to TRUE means that if
a job fails while running, it will be restarted from the beginning point
of the job.

Note that setting this attribute to TRUE might
lead to data inconsistencies in some situations, for example, if data is
committed within a job.

Retries on errors are not counted as regular runs. The run
count or failure count is not incremented until the job succeeds or has
failed all its six retries.

The restartable attribute is used by the Scheduler to
determine whether to retry the job not only on regular application errors,
but after a database malfunction as well. The Scheduler will retry the job
a maximum of six times. The first time, it will wait for one second and
multiply this wait time with a factor of 10 each time thereafter.

Both the run count and failure count are incremented by 1 if
the job has failed all its six retries. If the job immediately succeeds,
or it succeeds on one of its retries, run count is incremented by 1.

The Scheduler will stop retrying a job when:

-one of the retries succeeds

-all of its six retries have failed

-the next retry would occur after the next regularly scheduled
run of the job

The Scheduler no longer retries the job if the next scheduled
retry is past the next regularly scheduled run for repeating
jobs.

max_failures

This attribute specifies the number of times a job can fail on
consecutive scheduled runs before it is automatically disabled. Once a job
is disabled, it is no longer executed and its STATE is set to
BROKEN in the *_SCHEDULER_JOB views.

max_failures can be an integer between 1 to
1,000,000. By default, it is set to NULL, which indicates
that new instances of the job will be started regardless of how many
previous instances have failed.

max_runs

This attribute specifies the maximum number of consecutive
scheduled runs of the job. Once max_runs is reached, the job
is disabled and its state is changed to COMPLETED.

max_runs can be an integer between 1 and
1,000,000. By default, it is set to NULL, which means that it
will repeat forever or until end_date or
max_failures is reached.

job_weight

This attribute is for expert users of parallel technology
only. If your job will be using parallel technology, you can set the value
of this attribute to the degree of parallelism of your SQL inside the job.

job_weight has a range of 1-100, with 1 being the
default

instance_stickiness

This attribute should only be used for a database running in
RAC mode. By default, it is set to TRUE. If you set
instance_stickiness to TRUE, jobs start running
on the instance with the lightest load and the Scheduler thereafter
attempts to run on the instance that it last ran on. If that instance is
either down or so overloaded that it will not start new jobs for a
significant period of time, another instance will run the job. If the
interval between runs is large, instance_stickiness will be
ignored an the job will be handled as if it were a non-sticky job.

If instance_stickiness is set to
FALSE, each instance of the job runs on the first instance
available.

For non-RAC environments, this attribute is not useful because
there is only one instance.

stop_on_window_close

This attribute only applies if the schedule of a job is a
window or a window group. Setting this attribute to TRUE
implies that the job should be stopped once the associated window is
closed. The job is stopped using the stop_job procedure with
force set to FALSE.

By default, stop_on_window_close is set to
FALSE. Therefore, if you do not set this attribute, the job
will be allowed to continue after the window closes.

Note that, although the job is allowed to continue, its
resource allocation will probably change because closing a window
generally also implies a change in resource plans.

job_priority

This attribute specifies the priority of this job relative to
other jobs in the same class as this job. If multiple jobs within a class
are scheduled to be executed at the same time, the job priority determines
the order in which jobs from that class are picked up for execution by the
job coordinator. It can be a value from 1 through 5, with 1 being the
first to be picked up for job execution.

If no job priority is specified when creating a job, the
default priority of 3 is assigned to it.

schedule_limit

In heavily loaded systems, jobs are not always started at
their scheduled time. This attribute enables you to have the Scheduler not
start a job at all if the delay in starting the job is larger than the
interval specified. It can be a value of 1 minute to 99 days. For example,
if a job was supposed to start at noon and the schedule limit is set to 60
minutes, the job will not be run if it has not started to run by 1PM.

If schedule_limit is not specified, the job is
executed at some later date as soon as there are resources available to
run it. By default, this attribute is set to null, which indicates that
the job can be run at any time after its scheduled time. A scheduled job
run that is skipped because of this attribute does not count against the
number of runs and failures of the job. An entry in the job log will be
made to reflect the skipped run.

program_name

The name of a program object to use with this job. If this is
set, job_action, job_type and
number_of_arguments should be NULL.

job_action

This is a string specifying the action. The possible values
are:

PLSQL_BLOCK: a PLSQL anonymous block

STORED_PROCEDURE: name of the database stored
procedure (C, Java or PL/SQL), optionally qualified with a schema and/or
package name).

EXECUTABLE: Name of an executable of shell script
including the full path name and any command-line flags to it.

If this is set, program_name should be
NULL.

job_type

The type of this job. Can be any of: PLSQL_BLOCK,
STORED_PROCEDURE, and EXECUTABLE.

If this is set, program_name should be
NULL.

number_of_arguments

The number of arguments if the program is inlined. If this is
set, program_name should be NULL.

schedule_name

The name of a schedule or window or window group to use as the
schedule for this job. If this is set, end_date,
start_date and repeat_interval should all be
NULL.

repeat_interval

Either a PL/SQL function returning the next date on which to
run, or calendar syntax expression. If this is set,
schedule_name should be NULL.

start_date

The original date on which this job started or will be
scheduled to start. If this is set, schedule_name should be
NULL.

end_date

The date after which the job will no longer run. It will be
dropped if auto_drop is set or disabled with the state
changed to COMPLETED if it is. If this is set,
schedule_name should be NULL.

job_class

The class this job is associated with.

comments

An optional comment.

auto_drop

Whether the job should be dropped after having
completed.

Program Attribute
Values

Table 83-35
lists program attribute values.

Table 83-35  Program Attribute
Values

Name

Description

program_action

This is a string specifying the action. The possible values
are:

PLSQL_BLOCK: a PLSQL anonymous block

STORED_PROCEDURE: name of the database stored
procedure (C, Java or PL/SQL), optionally qualified with a schema and/or
package name).

EXECUTABLE: Full path name including the name of
the operating system executable or shell script.

program_type

The type of program. This must be one of the following
supported program types: PLSQL_BLOCK,
STORED_PROCEDURE, and EXECUTABLE.

number_of_arguments

The number of arguments of the program that can be set by any
job using it, these arguments must be defined before the program can be
enabled.

comments

An optional comment. This can describe what the program does,
or give usage details.

Job Class
Values

Table 83-36
lists job class attribute values.

Table 83-36  Job Class Attribute
Values

Name

Description

resource_consumer_group

The resource consumer group a class is associated with. If
resource_consumer_group is set, service must be
NULL.

service

The service the job class belongs to. The default is
NULL, which implies the default service. This should be the
name of the service database object and not the service name as defined in
tnsnames.ora. If service is set,
resource_consumer_group must be NULL.

logging_level

This attribute specifies how much information is logged. The
three possible options are:

DBMS_SCHEDULER.LOGGING_OFF

No logging will be performed for any jobs in this class.

DBMS_SCHEDULER.LOGGING_RUNS

The Scheduler will write detailed information to the job log
for all runs of each job in this class.

DBMS_SCHEDULER.LOGGING_FULL

In addition to recording every run of a job, the Scheduler
will record all operations performed on all jobs in this class. In other
words, every time a job is created, enabled, disabled, altered, and so on
will be recorded in the log.

log_history

This enables you to control the amount of logging the
Scheduler performs. To prevent the job log and the window log from growing
indiscriminately, the Scheduler has an attribute that specifies how much
history (in days) to keep. Once a day, the Scheduler will automatically
purge all log entries from both the job log as well as the window log that
are older than the specified history. The default is 30 days.

You can change the default by using the
SET_SCHEDULER_ATTRIBUTE procedure. For example, to change it
to 90 days, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('log_history','90');

The range of valid values is 1 through 999.

comments

An optional comment about the class.

Window Attribute
Values

Table 83-37
lists window attribute values.

Table 83-37  Window Attribute
Values

Name

Description

resource_plan

The resource plan to be associated with a window. When the
window opens, the system will switch to using this resource plan. When the
window closes, the original resource plan will be restored. If a resource
plan has been made active with the force option, no resource
plan switch will occur.

window_priority

The priority of the window. Must be one of LOW
(default) or HIGH.

duration

The duration of the window.

schedule_name

The name of a schedule to use with this window. If this is
set, start_date, end_date, and
repeat_interval must all be NULL.

repeat_interval

A string using the calendar syntax. PL/SQL date functions are
not allowed. If this is set, schedule_name must be
NULL.

start_date

The next date on which this window is scheduled to open. If
this is set, schedule_name must be
NULL.

end_date

The date after which the window will no longer open. If this
is set, schedule_name must be NULL.

comments

An optional comment about the
window.

Program Window Group
Values

Table 83-38
lists program window group values.

Table 83-38  Window Group Attribute
Values

Name

Description

comments

An optional comment about the window
group.

Schedule Attribute
Values

Table 83-39
lists schedule attribute values.

Table 83-39  Schedule Attribute
Values

Name

Description

repeat_interval

An expression using the calendar syntax.

comments

An optional comment.

end_date

The cutoff date after which the schedule will not specify any
dates.

start_date

The start or reference date used by the calendar
syntax.

SET_ATTRIBUTE_NULL Procedure

This procedure sets an attribute of a Scheduler object to
NULL. What attributes can be set depends on the object being
altered. If the object is enabled, it will be disabled before being altered and
be reenabled afterward. If the object cannot be re-enabled, an error is
generated and the object will be left in a disabled state.

Syntax

DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
name IN VARCHAR2,
attribute IN VARCHAR2);

Parameters

Table 83-40  SET_ATTRIBUTE_NULL
Procedure Parameters

Parameter

Description

name

The name of the object.

attribute

The attribute being changed.

Usage
Notes

To run SET_ATTRIBUTE_NULL for a window, window group,
or job class, you must have the MANAGE SCHEDULER
privilege. Otherwise, you must be the owner of the object being altered or have
ALTER privileges on that object or have the CREATE
ANY JOB privilege.

SET_JOB_ANYDATA_VALUE Procedures

This procedure sets a value to an argument of the associated program
for a job, encapsulated in an AnyData object. It overrides any
default value set for the program argument. This does not affect whether the job
is enabled or not. NULL is a valid assignment for a program
argument. Arguments can be specified by position or by name. No type checking of
the argument is done at any time by the Scheduler.

SET_JOB_ANYDATA_VALUE is overloaded.

Syntax

Sets a program argument by its position.

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN SYS.ANYDATA);

Sets a program argument by its name.

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN SYS.ANYDATA);

Parameters

Table 83-41  SET_JOB_ANYDATA_VALUE
Procedure Parameters

Parameter

Description

job_name

The name of the job to be altered.

argument_name

The name of the program argument being set.

argument_position

The position of the program argument being set.

argument_value

The new value to be assigned to the program argument,
encapsulated in an AnyData object.

Usage
Notes

SET_JOB_ANYDATA_VALUE requires that you be the owner of
the job or have ALTER privileges on that job. You can also set a
job argument value if you have the CREATE ANY
JOB privilege.

SET_JOB_ARGUMENT_VALUE Procedures

This procedure sets a value to an argument of the associated program
for a job. It overrides any default value set for the program argument. This
does not affect whether a job is enabled or not. NULL is a valid
assignment for a program argument. Arguments can be specified by position or by
name. No type checking of the argument is done at any time by the
Scheduler.

SET_JOB_ARGUMENT_VALUE is overloaded.

Syntax

Sets an argument value by position:

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN VARCHAR2);

Sets an argument value by name:

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN VARCHAR2);

Parameters

Table 83-42  SET_JOB_ARGUMENT_VALUE
Procedure Parameters

Parameter

Description

job_name

The name of the job to be altered.

argument_name

The name of the program argument being set.

argument_position

The position of the program argument being set.

argument_value

The new value to be set for the program argument. To set a
non-VARCHAR value, use the
SET_JOB_ANYDATA_ARGUMENT_VALUE
procedure.

Usage
Notes

SET_JOB_ARGUMENT_VALUE requires that you be the owner
of the job or have ALTER privileges on that job. You can also set a
job argument value if you have the CREATE ANY
JOB privilege.

SET_SCHEDULER_ATTRIBUTE Procedure

This procedure sets the value of a Scheduler attribute. This takes
effect immediately but the resulting changes may not be seen immediately. The
attributes you can set are default_timezone, max_job_slave_processes and log
history.

Syntax

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
attribute IN VARCHAR2,
value IN VARCHAR2);

Parameters

Table 83-43
SET_SCHEDULER_ATTRIBUTE Procedure Parameters

Parameter

Description

attribute

The name of the Scheduler attribute. Possible values are:

default_timezone: It is very important
that this attribute is set. Whenever a repeat_interval is specified
without setting the start_date, the scheduler needs to know which time
zone it must apply to the repeat interval syntax. For example, if the
repeat interval is specified as
"FREQ=DAILY;BYHOUR=22"

the job will repeat every day at 10pm, but 10pm in which
time zone? If no start_date is specified the scheduler will
pick up the time zone from this default_timezone attribute.
If you want your job or window to follow daylight savings adjustments,
you must set this attribute to the proper region name. For instance, if
your database resides in Paris, you would set this to
'Europe/Warsaw'.

Daylight saving adjustments will not be followed if you
specify an absolute offset, for instance '-8:00' would only be correct
for half of the year in San Francisco. If no value is specified for this
attribute, the scheduler uses the time zone of systimestamp
when the job or window is enabled. This is always an absolute offset and
will not follow daylight savings adjustments.

log_history:
This enables you to control the amount of logging the Scheduler
performs.

max_job_slave_processes: This enables you
to set a maximum number of slave processes for a particular system
configuration and load. The default value is NULL, and the
valid range is 1-999.

Even though the Scheduler automatically determines what the
optimum number of slave processes is for a given system configuration
and load, you still might want to set a fixed limit on the Scheduler. If
this is the case, you can set this attribute.

Although the number set by
max_job_slave_processes is a real maximum, it does not mean
the Scheduler will start the specified number of slaves. For example,
even though this attribute is set to 10, the Scheduler might still
determine that is should not start more than 3 slave processes. However,
if it wants to start 15, but it is set to 10, it will not start more
than 10.

value

The new value of the attribute.

Usage
Notes

To run SET_SCHEDULER_ATTRIBUTE, you must have the
MANAGE SCHEDULER privilege.

STOP_JOB
Procedure

This procedure stops currently running jobs or all jobs in a job
class. Any instance of the job will be stopped. After stopping the job, the
state of a one-time job will be set to SUCCEEDED whereas the state
of a repeating job will be set to SCHEDULED or
COMPLETED depending on whether the next run of the job is
scheduled.

Syntax

DBMS_SCHEDULER.STOP_JOB (
job_name IN VARCHAR2
force IN BOOLEAN DEFAULT FALSE);

Parameters

Table 83-44  STOP_JOB Procedure
Parameters

Parameter

Description

job_name

The name of the job or job class. Can be a comma-delimited
list. For a job class, the SYS schema should be specified.

If the name of a job class is specified, the jobs that belong
to that job class are stopped. The job class is not affected by this
call.

force

If force is set to FALSE, the
Scheduler tries to gracefully stop the job using an interrupt mechanism.
This method gives control back to the slave process, which can update the
status of the job in the job queue to stopped. If this fails, an error is
returned.

If force is set to TRUE, the
Scheduler will immediately terminate the job slave. Oracle recommends that
STOP_JOB with force set to TRUE be used only
after a STOP_JOB with force set to
FALSE has failed.

Use of the force option requires the
MANAGE SCHEDULER system privilege.

Setting force to TRUE is not
supported for jobs of type executable.

Usage
Notes

STOP_JOB without the force option requires
that you be the owner of the job or have ALTER privileges on that
job. You can also stop a job if you have the CREATE
ANY JOB or MANAGE SCHEDULER
privilege.

STOP_JOB with the force option requires
that have the MANAGE SCHEDULER privilege.

Previous

Next

Copyright © 1998, 2003 Oracle Corporation
All Rights Reserved.

Home

Book List

Contents

Index

Master Index

Feedback