128 DBMS_SCHEDULER

Data Structures

The DBMS_SCHEDULER package defines OBJECT types and TABLE types.

OBJECT Types

• JOBARG Object Type

• JOB Object Type

• JOB_DEFINITION Object Type

• JOBATTR Object Type

• SCHEDULER$_STEP_TYPE Object Type

• SCHEDULER$_EVENT_INFO Object Type

• SCHEDULER_FILEWATCHER_RESULT Object Type

• SCHEDULER_FILEWATCHER_REQUEST Object Type

TABLE Types

• JOBARG_ARRAY Table Type

• JOB_ARRAY Table Type

• JOB_DEFINITION_ARRAY Table Type

• JOBATTR_ARRAY Table Type

• SCHEDULER$_STEP_TYPE_LIST Table Type

JOBARG Object Type

This type is used by the JOB and JOBATTR object types. It represents a job argument in a batch of job arguments.

Syntax

TYPE jobarg IS OBJECT (
   arg_position         NUMBER,
   arg_text_value       VARCHAR2(4000),
   arg_anydata_value    ANYDATA,
   arg_operation        VARCHAR2(5));

Attributes

JOBARG Constructor Function

This constructor function constructs a job argument. It is overloaded to construct job arguments with different types of values.

Syntax

Constructs a job argument with a text value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_value           IN VARCHAR2)
   RETURN SELF AS RESULT;

Constructs a job argument with an AnyData value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_value           IN ANYDATA)
   RETURN SELF AS RESULT;

Constructs a job argument with a NULL value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_reset           IN BOOLEAN DEFAULT FALSE)
   RETURN SELF AS RESULT;

Parameters

JOBARG_ARRAY Table Type

Syntax

TYPE jobarg_array IS TABLE OF jobarg;

JOB Object Type

**** Deprecated. Use the JOB_DEFINITION object type instead.

JOB_ARRAY Table Type

*** Deprecated. Use the JOB_DEFINITION_ARRAY table type instead.

JOB_DEFINITION Object Type

This type is used by the CREATE_JOBS procedure and represents a job in a batch of jobs.

Syntax

TYPE job_definition IS OBJECT (
  job_name                       VARCHAR2(100),
  job_class                      VARCHAR2(32),
  job_style                      VARCHAR2(11),
  program_name                   VARCHAR2(100),
  job_action                     VARCHAR2(4000),
  job_type                       VARCHAR2(20),
  schedule_name                  VARCHAR2(65),
  repeat_interval                VARCHAR2(4000),
  schedule_limit                 INTERVAL DAY TO SECOND,
  start_date                     TIMESTAMP WITH TIME ZONE,
  end_date                       TIMESTAMP WITH TIME ZONE,
  event_condition                VARCHAR2(4000),
  queue_spec                     VARCHAR2(100),
  number_of_arguments            NUMBER,
  arguments                      SYS.JOBARG_ARRAY,
  job_priority                   NUMBER,
  job_weight                     NUMBER,
  max_run_duration               INTERVAL DAY TO SECOND,
  max_runs                       NUMBER,
  max_failures                   NUMBER,
  logging_level                  NUMBER,
  restartable                    VARCHAR2(5),
  stop_on_window_close           VARCHAR2(5),
  raise_events                   NUMBER,
  comments                       VARCHAR2(240),
  auto_drop                      VARCHAR2(5),
  enabled                        VARCHAR2(5),
  follow_default_timezone        VARCHAR2(5),
  parallel_instances             VARCHAR2(5),
  aq_job                         VARCHAR2(5),
  instance_id                    NUMBER,
  credential_name                VARCHAR2(65),
  destination                    VARCHAR2(4000),
  database_role                  VARCHAR2(20),
  allow_runs_in_restricted_mode  VARCHAR2(5));

Object Attributes

Table 128-3 provides brief descriptions of the attributes of the JOB_DEFINITION object type. For more complete information about these attributes, see the "CREATE_JOB Procedure" and the "SET_ATTRIBUTE Procedure".

JOB_DEFINITION Constructor Function

This constructor function constructs a job_definition object.

Syntax

constructor function job_definition (
    job_name                IN     VARCHAR2,
    job_style               IN     VARCHAR2 DEFAULT 'REGULAR',
    program_name            IN     VARCHAR2 DEFAULT NULL,
    job_action              IN     VARCHAR2 DEFAULT NULL,
    job_type                IN     VARCHAR2 DEFAULT NULL,
    schedule_name           IN     VARCHAR2 DEFAULT NULL,
    repeat_interval         IN     VARCHAR2 DEFAULT NULL,
    event_condition         IN     VARCHAR2 DEFAULT NULL,
    queue_spec              IN     VARCHAR2 DEFAULT NULL,
    start_date              IN     TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    end_date                IN     TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    number_of_arguments     IN     NATURAL DEFAULT NULL,
    arguments               IN     SYS.JOBARG_ARRAY DEFAULT NULL,
    job_class               IN     VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
    schedule_limit          IN     INTERVAL DAY TO SECOND DEFAULT NULL,
    job_priority            IN     NATURAL DEFAULT NULL,
    job_weight              IN     NATURAL DEFAULT NULL,
    max_run_duration        IN     INTERVAL DAY TO SECOND DEFAULT NULL,
    max_runs                IN     NATURAL DEFAULT NULL,
    max_failures            IN     NATURAL DEFAULT NULL,
    logging_level           IN     NATURALN DEFAULT 64,
    restartable             IN     BOOLEAN DEFAULT FALSE,
    stop_on_window_close    IN     BOOLEAN DEFAULT FALSE,
    raise_events            IN     NATURAL DEFAULT NULL,
    comments                IN     VARCHAR2 DEFAULT NULL,
    auto_drop               IN     BOOLEAN DEFAULT TRUE,
    enabled                 IN     BOOLEAN DEFAULT FALSE,
    follow_default_timezone IN     BOOLEAN DEFAULT FALSE,
    parallel_instances      IN     BOOLEAN DEFAULT FALSE,
    aq_job                  IN     BOOLEAN DEFAULT FALSE,
    instance_id             IN     NATURAL DEFAULT NULL,
    credential_name         IN     VARCHAR2 DEFAULT NULL,
    destination             IN     VARCHAR2 DEFAULT NULL,
    database_role           IN     VARCHAR2 DEFAULT NULL,
    allow_runs_in_restricted_mode IN BOOLEAN DEFAULT FALSE)
    RETURN SELF AS RESULT;

JOB_DEFINITION_ARRAY Table Type

Syntax

TYPE job_definition_array IS TABLE OF job_definition;

JOBATTR Object Type

This type is used by the SET_JOB_ATTRIBUTES procedure and represents a job attribute in a batch of job attributes.

Syntax

TYPE jobattr IS OBJECT (
   job_name             VARCHAR2(100),
   attr_name            VARCHAR2(30),
   char_value           VARCHAR2(4000),
   char_value2          VARCHAR2(4000),
   args_value           JOBARG_ARRAY,
   num_value            NUMBER,
   timestamp_value      TIMESTAMP(6) WITH TIME ZONE,
   interval_value       INTERVAL DAY(2) TO SECOND(6));

Attributes

JOBATTR Constructor Function

This constructor function constructs a job attribute. It is overloaded to create attribute values of the following types: VARCHAR2, NUMBER, TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND, and an array of JOBARG types.

Syntax

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2,
   attr_value          IN VARCHAR2,
   attr_value2         IN VARCHAR2 DEFAULT NULL)
   RETURN SELF AS RESULT;

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2,
   attr_value          IN [NUMBER, BOOLEAN,
                           TIMESTAMP WITH TIME ZONE,
                           INTERVAL DAY TO SECOND, JOBARG_ARRAY])
   RETURN SELF AS RESULT;

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2)
   RETURN SELF AS RESULT;

Parameters

JOBATTR_ARRAY Table Type

Syntax

TYPE jobattr_array IS TABLE OF jobattr;

SCHEDULER$_STEP_TYPE Object Type

This type is used by RUN_CHAIN to return a list of chain steps with an initial state.

Syntax

TYPE scheduler$_step_type IS OBJECT (
   step_name  VARCHAR2(32),
   step_type  VARCHAR2(32));

Attributes

SCHEDULER$_STEP_TYPE_LIST Table Type

Syntax

TYPE scheduler$_step_type_list IS TABLE OF scheduler$_step_type;

SCHEDULER$_EVENT_INFO Object Type

This the data type of the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE, from which your application consumes job state events raised by the Scheduler. It is a secure queue owned by SYS.

Syntax

TYPE SCHEDULER$_EVENT_INFO IS OBJECT (
  event_type         VARCHAR2(4000),
  object_owner       VARCHAR2(4000),
  object_name        VARCHAR2(4000),
  event_timestamp    TIMESTAMP WITH TIME ZONE,
  error_code         NUMBER,
  error_msg          VARCHAR2(4000),
  event_status       NUMBER,
  log_id             NUMBER,
  run_count          NUMBER,
  failure_count      NUMBER,
  retry_count        NUMBER,
  spare1             NUMBER,
  spare2             NUMBER,
  spare3             VARCHAR2(4000),
  spare4             VARCHAR2(4000),
  spare5             TIMESTAMP WITH TIME ZONE,
  spare6             TIMESTAMP WITH TIME ZONE,
  spare7             RAW(2000),
  spare8             RAW(2000));

Attributes

SCHEDULER_FILEWATCHER_RESULT Object Type

This is the data type of a file arrival event message. You access the event message as a parameter of an event-based job (or a parameter of a program referenced by an event-based job). The message contains information needed to locate and process a file that arrived on a local or remote system.

Syntax

TYPE scheduler_filewatcher_result IS OBJECT (
  destination         VARCHAR2(4000),
  directory_path      VARCHAR2(4000),
  actual_file_name    VARCHAR2(4000),
  file_size           NUMBER,
  file_timestamp      TIMESTAMP WITH TIME ZONE,
  ts_ms_from_epoch    NUMBER,
  matching_requests   SYS.SCHEDULER_FILEWATCHER_REQ_LIST);

Attributes

SCHEDULER_FILEWATCHER_REQUEST Object Type

This type is returned in the matching_requests attribute of the SCHEDULER_FILEWATCHER_RESULT Object Type. Its attributes are similar to the attributes of a file watcher.

Syntax

TYPE scheduler_filewatcher_request IS OBJECT (
  owner                 VARCHAR2(4000),
  name                  VARCHAR2(4000),
  requested_path_name   VARCHAR2(4000),
  requested_file_name   VARCHAR2(4000),
  credential_owner      VARCHAR2(4000),
  credential_name       VARCHAR2(4000),
  min_file_size         NUMBER,
  steady_state_dur      NUMBER);

Attributes

Using DBMS_SCHEDULER

This section contains:

• Security Model

• Rules and Limits

• Operational Notes

Security Model

The DBMS_SCHEDULER package ignores privileges granted on scheduler objects, such as jobs or chains, through roles. Object privileges must be granted directly to the user.

Rules and Limits

The following rules apply when using the DBMS_SCHEDULER package:

• Only SYS can perform actions on objects in the SYS schema.

• Several of the procedures accept comma-delimited lists of object names. If you provide a list of names, then the Scheduler stops executing the list at the first object that returns an error. Therefore, the Scheduler does not perform the tasks needed for the remaining objects on the list.

  For example, consider the statement DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3');

  If job3 cannot be stopped, then the jobs that follow it, jobclass1, jobclass2, and jobclass3 cannot be stopped. The jobs that preceded job3, job1 and job2, are stopped.

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

Operational Notes

The Scheduler uses a rich calendaring syntax to enable you to define repeating schedules, such as "every Tuesday and Friday at 4:00 p.m." or "the second Wednesday of every month." This calendaring syntax is used in calendaring expressions in the repeat_interval argument of a number of package subprograms. Evaluating a calendaring expression results in a set of discrete timestamps.

See Oracle Database Administrator's Guide for examples of the calendaring syntax.

Calendaring Syntax

This section starts with the calendaring syntax. It is followed by descriptions of various parts of the syntax.

In the calendaring syntax, * means 0 or more.

repeat_interval = regular_schedule | combined_schedule
 
regular_schedule = frequency_clause
[";" interval_clause] [";" bymonth_clause] [";" byweekno_clause]
[";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause]
[";" byday_clause] [";" byhour_clause] [";" byminute_clause]
[";" bysecond_clause] [";" bysetpos_clause] [";" include_clause]
[";" exclude_clause] [";" intersect_clause][";" periods_clause]
[";" byperiod_clause]
 
combined_schedule = schedule_list

frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )
predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | 
   "HOURLY" | "MINUTELY" | "SECONDLY"
user_defined_frequency = named_schedule

interval_clause = "INTERVAL" "=" intervalnum
   intervalnum = 1 through 99
bymonth_clause = "BYMONTH" "=" monthlist
   monthlist = month ( "," month)*
   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 = weeknumber ( "," weeknumber)*
   weeknumber = [minus] weekno
   weekno = 1 through 53
byyearday_clause = "BYYEARDAY" "=" yearday_list
   yearday_list = yearday ( "," yearday)*
   yearday = [minus] yeardaynum
   yeardaynum = 1 through 366
bydate_clause = "BYDATE" "=" date_list
   date_list = date ( "," date)*
   date = [YYYY]MMDD [ offset | span ]
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
bysetpos_clause = "BYSETPOS" "=" setpos_list
   setpos_list = setpos ("," setpos)*
   setpos = [minus] setpos_num
   setpos_num = 1 through 9999

include_clause = "INCLUDE" "=" schedule_list
exclude_clause = "EXCLUDE" "=" schedule_list
intersect_clause = "INTERSECT" "=" schedule_list
schedule_list = schedule_clause ("," schedule_clause)*
schedule_clause = named_schedule [ offset ]
named_schedule = [schema "."] schedule
periods_clause = "PERIODS" "=" periodnum
byperiod_clause = "BYPERIOD" "=" period_list
period_list = periodnum ("," periodnum)*
periodnum = 1 through 100

offset = ("+" | "-") ["OFFSET:"] duration_val
span = ("+" | "-" | "^") "SPAN:" duration_val
duration_val = dur-weeks | dur_days
dur_weeks = numofweeks "W"
dur_days = numofdays "D"
numofweeks = 1 through 53
numofdays = 1 through 376
minus = "-"

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

3/31/2005, 6/30/2005, 9/30/2005, 12/31/2005

3/31/2005, 4/30/2005, 6/30/2005, 9/30/2005, 12/31/2005

FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=last_sat,end_qtr

FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4

Combining Schedules There are two ways to combine schedules:

• Using a combined schedule expression, which is a list of individual schedules

  For example, to create a schedule for all company holidays, you provide a list of individual schedules, where each schedule in the list defines a single holiday. The Scheduler evaluates each individual schedule, and then returns a union of the timestamps returned by each individual schedule.

• Embedding other schedules into the main schedule using include, exclude, and intersect clauses

  With this method, the embedded schedules inherit certain attributes from the main schedule.

  • Timestamps generated by the INCLUDE clause that fall into periods that are skipped by the main schedule are ignored. This is the case when the main schedule skips periods due to the INTERVAL clause, the BYPERIOD clause, or the BYMONTH clause for freq=monthly.

  • Days that are added by the INCLUDE clause follow the hourly/minutely/secondly execution pattern of the main schedule.

  • When the INCLUDE clause is present, no date-specific defaults are retrieved from the start date (but time-specific defaults can be). (See "Start Dates and Repeat Intervals", later in this section.) For example, a repeat_interval of FREQ=MONTHLY;INCLUDE=HOLIDAY executes only on holidays and not on the month/day defaults retrieved from the start date.

  The following is an example:

  BEGIN
  dbms_scheduler.create_schedule('embed_sched', repeat_interval =>
    'FREQ=YEARLY;BYDATE=0130,0220,0725');
  dbms_scheduler.create_schedule('main_sched', repeat_interval =>
    'FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=15;BYHOUR=9,17;INCLUDE=embed_sched');
  END;
  /
  

  In this example, the dates 1/30, 2/20, and 7/25 are added to the main schedule. However, the Scheduler does not include dates that fall in months that are skipped by the INTERVAL clause. If the start date of the main schedule is 1/1/2005, then 2/20 is not added. On the dates that are added, the embedded schedule follows the execution pattern of the main schedule: jobs are executed at 9:00 a.m. and 5:00 p.m. on 1/30 and 7/25. If the embedded schedule does not itself have a start date, it inherits the start date from the main schedule.

User-Defined Frequencies Instead of using predefined frequencies like DAILY, WEEKLY, MONTHLY, and so on, you can create your own frequencies by creating a schedule that returns the start date of each period. For example, the following repeat_interval expression is used in a schedule named fiscal_year that defines the start of each quarter in a fiscal year:

FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4

To return the last Wednesday of every quarter, you create a schedule (the "main schedule") that uses the fiscal_year schedule as a user-defined frequency:

FREQ=fiscal_year;BYDAY=-1WED

Periods in a user-defined frequency do not have to be equal in length. In the main schedule, the BYSETPOS clause and numbered weekdays are recalculated based on the size of each period. To select dates in specific periods, you must use the BYPERIOD clause in the main schedule. To enable this, the schedule that is used as the user-defined frequency must include a PERIODS clause, and it must set its start date appropriately. The first date returned by this schedule is used as the starting point of period 1.

As another example, assuming work days are Monday through Friday, to get the last work day of the 2nd and 4th quarters of the fiscal year, the repeat_interval clause in the main schedule is the following:

FREQ=fiscal_year;BYDAY=MON,TUE,WED,THU,FRI;BYPERIOD=2,4;BYSETPOS=-1

Start Dates and Repeat Intervals The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, then the month and day that the job runs on are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, then the day of the month that the job runs on is retrieved from the start date. If present, BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified. Note that if the INCLUDE, EXCLUDE, or INTERSECT clauses are present, no date-related defaults are retrieved from the start date, but time-related defaults are.The following are some examples:

start_date:      4/15/05 9:00:00
repeat_interval: freq=yearly

is expanded internally to:

freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0

The preceding schedule executes on 04/15/05 9:00:00, 04/15/06 9:00:00, 04/15/07 9:00:00, and so on.

For the next example, assume that schedule S1 has a repeat_interval of FREQ=YEARLY;BYDATE=0701.

start_date:      01/20/05 9:00:00
repeat_interval: freq=yearly;include=S1

is expanded internally to:

freq=yearly;byhour=9;byminute=0;bysecond=0;include=S1

Because an INCLUDE clause is present, date-related information is not retrieved from the start date. However, time-specific information is, so the preceding schedule executes on 07/01/05 9:00:00, 07/01/06 9:00:00, 07/01/08 9:00:00, and so on.

General Rules When using a calendaring expression, consider the following rules:

• For a regular schedule (as opposed to a combined schedule), 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 semicolon, and each clause can be present at most once, with the exception of the include, exclude, and intersect clauses.

• 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.

• Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.

• 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, then you must 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 ensures 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', then daylight savings adjustments are not followed and your job execution is off by an hour for half the year.

• When start_date is NULL, the Scheduler determines the time zone for the repeat interval as follows:

  1. It checks whether or not 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';
       

     • Setting the ORA_SDTZ environment variable.

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

  3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler uses the time zone of systimestamp when the job or window is enabled.

BYSETPOS Clause Rules The following are rules for the BYSETPOS clause.

• The BYSETPOS clause is the last clause to be evaluated. It is processed after all other BY clauses and the INCLUDE, EXCLUDE and INTERSECT clauses have been evaluated.

• The INTERVAL clause does not change the size of the period to which the BYSETPOS clause is applied. For example, when the frequency is set to monthly and interval is set to 3, the list of timestamps to which BYSETPOS is applied is generated from a month, not a quarter. The only impact of the INTERVAL clause is to cause months to be skipped. However, you can still select the second to last workday of the quarter like this:

  FREQ=MONTHLY;INTERVAL=3;BYDAY=MON,TUE,WED,THU,FRI;BYSETPOS=-2
  

  provided that you set the start date in the right month. This example returns the next to last workday of a month, and repeats once a quarter.

• To get consistent results, the set to which BYSETPOS is applied is determined from the beginning of the frequency period independently of when the evaluation occurs. Whether the Scheduler evaluates

  FREQ=MONTHLY;BYDAY=MON,TUE,FRI;BYSETPOS=1,3
  

  on 01/01/2004 or 01/15/2004, in both cases the expression evaluates to Friday 01/02/2004, and Tuesday 01/06/2004. The only difference is that when the expression is evaluated on 01/15/2004, the Scheduler determines that there are no matches in January because the timestamps found are in the past, and it moves on to the matches in the next month, February.

BYDATE Clause Rules The following are rules for the BYDATE clause.

• If dates in the BYDATE clause do not have their optional year component, the job runs on those dates every year.

• The job execution times on the included dates are derived from the BY clauses in the calendaring expression. For example, if repeat_interval is defined as

  freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922
  

  then the execution times on 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m.

EXCLUDE Clause Rules Excluded dates without a time component are 24 hour periods. All timestamps that fall on an excluded date are removed. In the following example, jan_fifteen is a named schedule that resolves to the single date of 01/15:

freq=monthly;bymonthday=15,30;byhour=8,13,18;byminute=0;bysecond=0;
     exclude=jan_fifteenth

In this case, all three instances of the job are removed for 01/15.

OFFSET Rules You can adjust the dates of individual named schedules by adding positive offsets to them. For example, to execute JOB2 exactly 15 days after every occurrence of JOB1, add +OFFSET:15D to the schedule of JOB1, as follows:

BEGIN
dbms_scheduler.create_schedule('job2_schedule', repeat_interval =>
  'job1_schedule+OFFSET:15D');
END;
/

Note that negative offsets to named schedules are not supported.

begin
 dbms_scheduler.create_schedule('year_start', repeat_interval=>
       'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');
 dbms_scheduler.create_schedule('retail_fiscal_year',
        to_timestamp_tz('15-JAN-2005 12:00:00','DD-MON-YYYY HH24:MI:SS'),
         'year_start,year_start+13w,year_start+26w,year_start+39w;periods=4');
end;
/

begin
 dbms_scheduler.create_schedule('fifth_day_off', repeat_interval=>
  'FREQ=retail_fiscal_year;BYDAY=SAT,SUN;INCLUDE=holiday;
    BYPERIOD=2,4;BYSETPOS=5');
end;
/

Summary of DBMS_SCHEDULER Subprograms

ADD_EVENT_QUEUE_SUBSCRIBER Procedure

This procedure adds a user as a subscriber to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE, and grants the user permission to dequeue from this queue using the designated agent.

Syntax

DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER (
   subscriber_name         IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. If an AQ agent with the same name already exists, an error is raised.

ADD_GROUP_MEMBER Procedure

This procedure adds one or more members to an existing group.

Syntax

DBMS_SCHEDULER.ADD_GROUP_MEMBER (
   group_name              IN VARCHAR2,
   member                  IN VARCHAR2);

Parameters

Usage Notes

The following users may add members to a group:

• The group owner

• A user that has been granted the ALTER object privilege on the group

• A user with the CREATE ANY JOB system privilege

You must have the MANAGE SCHEDULER privilege to add a member to a group of type WINDOW.

ADD_JOB_EMAIL_NOTIFICATION Procedure

This procedure adds e-mail notifications for a job. E-mails are then sent to the specified list of recipients whenever any of the specified job state events is raised.

Syntax

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name             IN VARCHAR2,
    recipients           IN VARCHAR2,
    sender               IN VARCHAR2 DEFAULT NULL,
    subject              IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_SUBJECT,
    body                 IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_BODY,
    events               IN VARCHAR2 DEFAULT 'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,
                             JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR',
    filter_condition     IN VARCHAR2 DEFAULT NULL);

Parameters

'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%'

'Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%'

JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR

:event.error_code=600 or :event.error_code=700

Table 128-15 lists the variables that you can use in the subject and body arguments.

Usage Notes

You can call ADD_JOB_EMAIL_NOTIFICATION once for each different set of notifications that you want to configure for a particular job. For example, you may want to send notifications for the JOB_FAILED, JOB_BROKEN, JOB_SCH_LIM_REACHED, and JOB_CHAIN_STALLED events to the principle DBA and all senior DBAs, but send a notification for the JOB_OVER_MAX_DUR event only to the principle DBA.

This procedure succeeds only if the Scheduler attribute email_server is set to a valid SMTP server. See Oracle Database Administrator's Guide for more information.

To call this procedure, you must be the job owner or have the CREATE ANY JOB system privilege or have the ALTER object privilege on the job.

ADD_WINDOW_GROUP_MEMBER Procedure

*** Deprecated in Oracle Database 11g Release 2 (11.2). Use ADD_GROUP_MEMBER instead.

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

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.

ALTER_CHAIN Procedure

This procedure alters an attribute of the specified steps of a chain. This affects all future runs of the specified steps, both in the currently running chain job and in future runs of the same chain job or other chain jobs that point to the chain.

Syntax

Alters the value of a boolean attribute of one or more steps:

DBMS_SCHEDULER.ALTER_CHAIN (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   value                   IN BOOLEAN);

Alters the value of a character attribute of one or more steps:

DBMS_SCHEDULER.ALTER_CHAIN (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   char_value              IN VARCHAR2);

Parameters

Usage Notes

Altering a chain requires ALTER privileges on the chain either by being the owner of the chain, or by having the ALTER object privilege on the chain or by having the CREATE ANY JOB system privilege.

ALTER_RUNNING_CHAIN Procedure

This procedure alters an attribute of the specified steps of a chain. This affects only steps of the instance of the chain for the specified running chain job.

Syntax

DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (
   job_name                IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   value                   IN {BOOLEAN|VARCHAR2});

Parameters

Usage Notes

Altering a running chain requires you to have alter privileges on the job that is running (either as the owner, or as a user with ALTER privileges on the job or the CREATE ANY JOB system privilege).

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 switches the resource plan to the one that is 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

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 does not stop when the window it is running in closes, unless the attribute stop_on_window_close is set to TRUE for the job. However, the resources allocated to the job can change if the resource plan changes.

When a running job has a group of type WINDOW as its schedule, the job is not stopped when its window is closed if another window in the same window group becomes active. This is the case even if the job has the attribute stop_on_window_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

Usage Notes

To copy a job, you must have privileges to create a job in the schema of the new job (the CREATE JOB system privilege if it is in your own schema, otherwise, the CREATE ANY JOB system privilege). If the old job is not in the your own schema, then you must also have ALTER privileges on the old job or the CREATE ANY JOB system privilege.

CREATE_CHAIN Procedure

This procedure creates a new chain. The chain name can be optionally qualified with a schema name (for example, myschema.myname).

A chain is always created as disabled and must be enabled with the ENABLE Procedure before it can be used.

Syntax

DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name              IN VARCHAR2,
   rule_set_name           IN VARCHAR2 DEFAULT NULL,
   evaluation_interval     IN INTERVAL DAY TO SECOND DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

To create a chain in your own schema, you must have the CREATE JOB system privilege. To create a chain in a different schema you must have the CREATE ANY JOB system privilege. If you do not provide a rule_set_name, a rule set and evaluation context is created in the schema that the chain is being created in, so you must have the privileges required to create these objects. See the DBMS_RULE_ADM.CREATE_RULE_SET and DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedures for more information.

CREATE_CREDENTIAL Procedure

This procedure creates a stored username/password pair. Credentials are assigned to jobs so that they can authenticate with a local or remote host operating system or a remote Oracle database.

Syntax

DBMS_SCHEDULER.CREATE_CREDENTIAL (
   credential_name         IN VARCHAR2,
   username                IN VARCHAR2,
   password                IN VARCHAR2,
   database_role           IN VARCHAR2 DEFAULT NULL,
   windows_domain          IN VARCHAR2 DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Credentials reside in a particular schema and can be created by any user with the CREATE JOB system privilege. To create a credential in a schema other than your own, you must have the CREATE ANY JOB privilege.

CREATE_DATABASE_DESTINATION Procedure

This procedure creates a database destination. A database destination represents an Oracle database on which remote database jobs run.

The host that the remote database resides on must have a running Scheduler agent that is registered with the database that this procedure is called from.

Syntax

DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION (
   destination_name        IN VARCHAR2,
   agent                   IN VARCHAR2,
   tns_name                IN VARCHAR2,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Database destinations reside in a particular schema and can be created by any user with the CREATE JOB system privilege. To create a database destination in a schema other than your own, you must have the CREATE ANY JOB privilege.

CREATE_EVENT_SCHEDULE Procedure

This procedure creates an event schedule, which is used to start a job when a particular event is raised.

Syntax

DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name           IN VARCHAR2,
   start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   event_condition         IN VARCHAR2 DEFAULT NULL,
   queue_spec              IN VARCHAR2,
   end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

You must have the CREATE JOB privilege to create a schedule in your 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.

CREATE_FILE_WATCHER Procedure

This procedure creates a file watcher, which is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. After you create a file watcher, you reference it in an event-based job or event schedule.

Syntax

DBMS_SCHEDULER.CREATE_FILE_WATCHER (
   file_watcher_name            IN VARCHAR2,
   directory_path               IN VARCHAR2,
   file_name                    IN VARCHAR2,
   credential_name              IN VARCHAR2,
   destination                  IN VARCHAR2  DEFAULT NULL,
   min_file_size                IN PLS_INTEGER DEFAULT 0,
   steady_state_duration        IN INTERVAL DAY TO SECOND DEFAULT NULL,
   comments                     IN VARCHAR2 DEFAULT NULL,
   enabled                      IN BOOLEAN DEFAULT TRUE);

Parameters

Usage Notes

You must have the CREATE JOB system privilege to create a file watcher in your own schema. You require the CREATE ANY JOB system privilege to create a file watcher in a schema different from your own (except the SYS schema, which is disallowed).

CREATE_GROUP Procedure

This procedure creates a group. Groups contain members, which you can specify when you create the group or at a later time. There are three types of groups: window groups, database destination groups, and external destination groups.

You can use a group name in other DBMS_SCHEDULER package procedures to specify a list of objects. For example, to specify multiple destinations for a remote database job, you provide a group name for the DESTINATION_NAME parameter of the job.

Syntax

DBMS_SCHEDULER.CREATE_GROUP (
   group_name           IN VARCHAR2,
   group_type           IN VARCHAR2,
   member               IN VARCHAR2 DEFAULT NULL,
   comments             IN VARCHAR2 DEFAULT NULL);

Parameters

[[schema.]credential@][schema.]destination

Usage Notes

Groups reside in a particular schema and can be created by any user with the CREATE JOB system privilege. To create a group in a schema other than your own, you must have the CREATE ANY JOB privilege. The group name must be unique among all Scheduler objects.

You can grant the SELECT privilege on a group so that other users can reference the group when creating jobs or schedules. To enable other users to modify a group, you can grant the ALTER privilege on the group.

Each group member must be unique within the group. For destination groups, the credential/destination name pairs must be unique within the group. An error is generated if any of the group members do not exist. For destination groups, both the credential and destination portions of a member must exist.

Another group of the same type can be a group member. The Scheduler immediately expands the included group name into its list of members.

Groups are created enabled, but you can disable them.

This procedure supersedes the procedure CREATE_WINDOW_GROUP, which is deprecated in Oracle Database 11g Release 2 (11.2).

Example

The following PL/SQL block creates a group named production_dest1, whose members are database destinations for a collection of production databases.

BEGIN
  DBMS_SCHEDULER.CREATE_GROUP(
    GROUP_NAME    => 'production_dest1',
    GROUP_TYPE    => 'DB_DEST',
    MEMBER        => 'LOCAL, oracle_cred@prodhost1, prodhost2',
    COMMENTS      => 'All sector1 production machines');
END;

CREATE_JOB Procedure

This procedure creates a single job (regular or lightweight). If you create the job as enabled by setting the enabled attribute to TRUE, the Scheduler automatically runs the job according to its schedule. If you create the job disabled, the job does not run until you enable it with the SET_ATTRIBUTE Procedure.

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,
   credential_name      IN VARCHAR2                 DEFAULT NULL,
   destination_name     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,
   job_style               IN VARCHAR2              DEFAULT 'REGULAR',
   credential_name         IN VARCHAR2              DEFAULT NULL,
   destination_name        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,
   job_style            IN VARCHAR2                 DEFAULT 'REGULAR',
   credential_name      IN VARCHAR2                 DEFAULT NULL,
   destination_name     IN VARCHAR2                 DEFAULT NULL);

Creates a job using a named schedule object and an inlined 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,
   credential_name         IN VARCHAR2          DEFAULT NULL,
   destination_name        IN VARCHAR2          DEFAULT NULL);

Creates a job using an inlined program and an event:

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,
   event_condition       IN VARCHAR2          DEFAULT NULL,
   queue_spec             IN VARCHAR2,
   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,
   credential_name         IN VARCHAR2          DEFAULT NULL,
   destination_name        IN VARCHAR2          DEFAULT NULL);

Creates a job using a named program object and an event:

DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   program_name            IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIME ZONE,
   event_condition       IN VARCHAR2,
   queue_spec             IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIME ZONE,
   job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN           DEFAULT FALSE,
   auto_drop               IN BOOLEAN           DEFAULT TRUE,
   comments                IN VARCHAR2          DEFAULT NULL,
   job_style               IN VARCHAR2          DEFAULT 'REGULAR',
   credential_name         IN VARCHAR2          DEFAULT NULL,
   destination_name        IN VARCHAR2          DEFAULT NULL);

Parameters

credential.destination

Usage Notes

Jobs are created as disabled by default. You must explicitly enable them so that they will become active and scheduled. Before enabling a job, ensure that all program arguments, if any, are defined, either by defining default values in the program object or by supplying values with the job.

The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. Beginning with Oracle Database 11g Release 2 (11.2), JOB_QUEUE_PROCESSES applies to DBMS_SCHEDULER jobs. Setting this parameter to 0 disables DBMS_SCHEDULER jobs.

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. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

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

Not all possible job attributes can be set with CREATE_JOB. Some must be set after the job is created. For example, job arguments must be set with the SET_JOB_ARGUMENT_VALUE Procedure or the SET_JOB_ANYDATA_VALUE Procedure. Other job attributes, such as job_priority and max_runs, are set with the SET_ATTRIBUTE Procedure.

To create multiple jobs efficiently, use the CREATE_JOBS procedure.

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 DBMS_SCHEDULER.LOGGING_RUNS,
   log_history               IN PLS_INTEGER DEFAULT NULL,
   comments                  IN VARCHAR2 DEFAULT NULL);

Parameters

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_JOBS Procedure

This procedure creates multiple jobs (regular or lightweight) and sets the values of their arguments in a single call.

Syntax

DBMS_SCHEDULER.CREATE_JOBS (
   jobdef_array      IN SYS.JOB_DEFINITION_ARRAY,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Usage Notes

This procedure creates many jobs in the context of a single transaction. To realize the desired performance gains, the jobs being created must be grouped in batches of sufficient size. Calling CREATE_JOBS with a small array size may not be much faster than calling CREATE_JOB once for each job.

You cannot use this procedure to create multiple-destination jobs. That is, the destination attribute of the job_definition object cannot reference a destination group.

Examples

See Oracle Database Administrator's Guide.

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

Usage Notes

To create a program in their own schema, users need 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 parameter is set to TRUE). It cannot be executed by a job until it is enabled.

To use your programs, other users must have EXECUTE privileges, therefore once a program has been created, you have to grant EXECUTE privileges 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

Usage Notes

This procedure requires the CREATE JOB privilege to create a schedule in your 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.

CREATE_WINDOW Procedure

This procedure creates a recurring time window and associates it with a resource plan. You can then use the window to schedule jobs that run under the associated resource plan. Windows are created in the SYS schema.

The procedure is overloaded.

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

Usage Notes

Creating a window requires the MANAGE SCHEDULER privilege.

Scheduler windows are the principal mechanism used to automatically switch resource plans according to a schedule. You can also manually activate a resource plan by using the ALTER SYSTEM SET RESOURCE_MANAGER_PLAN statement or the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure. Note that either of these manual methods can also disable resource plan switching by Scheduler windows. For more information, see Oracle Database Administrator's Guide and "SWITCH_PLAN Procedure".

CREATE_WINDOW_GROUP Procedure

*** Deprecated in Oracle Database 11g Release 2 (11.2). Use CREATE_GROUP instead.

This procedure creates a new window group. A window group is defined by a list of Scheduler windows. You can assign a window group as a job schedule. The job then runs when any of the windows in the group become active.

Window groups are created in the SYS schema.

Syntax

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

Parameters

Usage Notes

Creating a window group requires the MANAGE SCHEDULER privilege. 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 name or default value for a program argument that is of a complex type and must be encapsulated within an ANYDATA object. A job that references the program can override the default value.

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

Usage Notes

All program arguments from one to the number_of_arguments value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.

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_CHAIN_EVENT_STEP Procedure

This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step does not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step replaces the old one.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   event_schedule_name     IN VARCHAR2,
   timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   event_condition         IN VARCHAR2,
   queue_spec              IN VARCHAR2,
   timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);

Parameters

Usage Notes

Defining a chain step requires ALTER privileges on the chain either as the owner of the chain, or as a user with the ALTER object privilege on the chain or the CREATE ANY JOB system privilege.

You can base a chain step on a file watcher as well. To do this, provide the file watcher name directly in the queue_spec parameter, or use a file watcher schedule for the event_schedule_name parameter.

DEFINE_CHAIN_RULE Procedure

This procedure adds a new rule to an existing chain, specified as a condition-action pair. The condition is expressed using either SQL or the Scheduler chain condition syntax and indicates the prerequisites for the action to occur. The action is a result of the condition being met.

An actual rule object is created to store the rule in the schema where the chain resides. If a rule name is given, this name is used for the rule object. If an existing rule name in the schema of the chain is given, the existing rule is altered. (A schema different than the schema of the chain cannot be specified). If no rule name is given, one is generated in the form SCHED_RULE${N}.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name              IN VARCHAR2,
   condition               IN VARCHAR2,
   action                  IN VARCHAR2,
   rule_name               IN VARCHAR2 DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Scheduler Chain Condition Syntax

The Scheduler chain condition syntax provides an easy way to construct a condition using the states and error codes of steps in the current chain. The following are the available constructs, which are all boolean expressions:

TRUE
FALSE
stepname [NOT] SUCCEEDED 
stepname [NOT] FAILED 
stepname [NOT] STOPPED 
stepname [NOT] COMPLETED 
stepname ERROR_CODE IN (integer, integer, integer ...)
stepname ERROR_CODE NOT IN (integer, integer, integer ...)
stepname ERROR_CODE = integer
stepname ERROR_CODE != integer
stepname ERROR_CODE <> integer
stepname ERROR_CODE > integer
stepname ERROR_CODE >= integer
stepname ERROR_CODE < integer
stepname ERROR_CODE <= integer

These boolean operators are available to create more complex conditions:

expression AND expression
expression OR expression
NOT (expression)

integer can be positive or negative. Parentheses may be used for clarity or to enforce ordering. You must use parentheses with the NOT operator.

PL/SQL code that runs as part of a step can set the value of ERROR_CODE for that step with the RAISE_APPLICATION_ERROR statement.

Usage Notes

Defining a chain rule requires ALTER privileges on the chain (either as the owner, or as a user with ALTER privileges on the chain or the CREATE ANY JOB system privilege).

You must define at least one rule that starts the chain and at least one that ends it. See the section "Adding Rules to a Chain" in Oracle Database Administrator's Guide for more information.

Examples

The following are examples of using rule conditions and rule actions.

Rule Conditions Using Scheduler Chain Condition Syntax

'step1 completed'
-- satisfied when step step1 has completed. (step1 completed is also TRUE when any 
-- of the following are TRUE: step1 succeeded, step1 failed, step1 stopped.)

'step1 succeeded and step2 succeeded'
-- satisfied when steps step1 and step2 have both succeeded

'step1 error_code > 100'
-- satisfied when step step1 has failed with an error_code greater than 100

'step1 error_code IN (1, 3, 5, 7)'
-- satisfied when step step1 has failed with an error_code of 1, 3, 5, or 7

Rule Conditions Using SQL Syntax

':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24'
--satisfied when step step1 completed less than an hour ago

':step1.duration > interval ''5'' minute'
-- satisfied when step step1 has completed and took longer than 5 minutes to complete

Rule Actions

'AFTER 01:00:00 START step1, step2'
--After an hour start steps step1 and step2

'STOP step1'
--Stop step step1

END step4.error_code'
--End the chain with the error code that step step4 finished with. If step4 has not completed, the chain will be ended unsuccessfully with error code 27435.

'END' or 'END 0'
--End the chain successfully (with error_code 0)

'END 100'
--End the chain unsuccessfully with error code 100.

DEFINE_CHAIN_STEP Procedure

This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name argument, the new step replaces the old one.

The chain owner must have EXECUTE privileges on the program or chain associated with the step. Only one program or chain can run during a step.

You cannot set all possible step attributes with this procedure. Use the ALTER_CHAIN procedure to set additional chain step attributes, such as credential_name and destination_name.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   program_name            IN VARCHAR2);

Parameters

Usage Notes

Defining a chain step requires ALTER privileges on the chain (either as the owner, or a user with ALTER privileges on the chain or the CREATE ANY JOB system privilege).

DEFINE_METADATA_ARGUMENT Procedure

This procedure defines a special metadata argument for the program. The Scheduler can pass Scheduler metadata through this argument to your stored procedure or other executable. You cannot set values for jobs using this argument.

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

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.

All metadata attributes except event_message can be used in PL/SQL blocks that you enter into the job_action or program_action attributes of jobs or programs, respectively. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value.

DEFINE_PROGRAM_ARGUMENT Procedure

This procedure defines a name or default value for a program argument. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.)

This procedure is overloaded.

Syntax

Defines a program argument without 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 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,
   default_value           IN VARCHAR2,
   out_argument            IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

All program arguments from 1 to the number_of_arguments value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.

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 only supports arguments of SQL type. Therefore, argument values that are not of SQL type, such as booleans, are not supported as program or job arguments.

DISABLE Procedure

This procedure disables a program, job, chain, window, database destination, external destination, file watcher, or group. When an object is disabled, its enabled attribute is set to FALSE.

Syntax

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

Parameters

Usage Notes

Windows must be preceded by SYS.

Disabling an object that is already disabled does not generate an error.

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

To run DISABLE for a window or a group of type WINDOW, you must have the MANAGE SCHEDULER privilege.

You can use DISABLE with any schema except the SYS schema.

Jobs

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.

For jobs with multiple destinations, you cannot disable a child job at a specific destination. Instead, you can disable the destination.

Programs

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 not be referenced 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

No arguments that pertain to the program are affected when the program is disabled.

File Watchers

If force is set to FALSE, the file watcher must not be referenced by any job, otherwise an error will occur. If you force disabling a file watcher, jobs that depend on it become disabled.

Windows

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 occurs.

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 reenabled.

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

Window Groups

When a group of type WINDOW is disabled, jobs (other than a running job) that have the window group as their schedule will not run when the member windows open. However, a job that has one of the window group members as its schedule still runs.

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.

Job Chains

When a chain is disabled, the metadata for the chain is still there, but jobs that point to it will not be able to be run. This allows changes to the chain to be made safely without the risk of having an incompletely specified chain run.If force is set to FALSE, the chain must not be referenced by any job, otherwise an error will occur.If force is set to TRUE, those jobs that point to the chain will not be disabled, however, they will fail at runtime.Running jobs that point to this chain are not affected by the DISABLE call and are allowed to complete.

Database Destinations

When you disable a database destination:

• The destination is skipped when a multiple destination job runs.

• If all destinations are disabled for a job, the Scheduler generates an error when it attempts to run the job.

• The REFS_ENABLED column in *_SCHEDULER_JOB_DESTS is set to FALSE for all jobs that reference the database destination.

External Destinations

When you disable an external destination:

• Dependent database destinations remain enabled, but the Scheduler generates an error when it attempts to run a job with a database destination that depends on the external destination.

• The REFS_ENABLED column in *_SCHEDULER_JOB_DESTS is set to FALSE for all external jobs that reference the external destination and for all database jobs with a database destination that depends on the external destination.

Groups

If you disable an external destination group or database destination group, the Scheduler generates an error when it attempts to run a job that names the group as its destination.

DROP_AGENT_DESTINATION Procedure

This procedure drops one or more external destinations, also known as agent destinations. It should be used only when the preferred method of dropping an external destination—using the schagent utility to unregister a Scheduler agent with a database—is unavailable due to failures.

This procedure can be called only by the SYS user or a user with the MANAGE SCHEDULER privilege.

Syntax

DBMS_SCHEDULER.DROP_AGENT_DESTINATION (
   destination_name        IN VARCHAR2);

Parameters

Usage Notes

When an external destination is dropped:

• All database destinations that refer to the external destination are disabled and their agent attribute is set to NULL.

• Members of external destination groups that refer to the destination are removed from the group.

• All job instances in the *_SCHEDULER_JOB_DESTS views that refer to the external destination are also dropped.

• Jobs running against the destination are stopped.

DROP_CHAIN Procedure

This procedure drops an existing chain.

Syntax

DBMS_SCHEDULER.DROP_CHAIN (
   chain_name              IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

Dropping a chain requires alter privileges on the chain (either as the owner, or a user with ALTER privileges on the chain or the CREATE ANY JOB system privilege).

All steps associated with the chain are dropped. If no rule set was specified when the chain was created, then the automatically created rule set and evaluation context associated with the chain are also dropped, so the user must have the privileges required to do this. See the DBMS_RULE_ADM.DROP_RULE_SET and DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT procedures for more information.

If force is FALSE, no jobs may be using this chain. If force is TRUE, any jobs that use this chain are disabled before the chain is dropped (and any of these jobs that are running will be stopped).

DROP_CHAIN_RULE Procedure

This procedure removes a rule from an existing chain. The rule object corresponding to this rule will also be dropped. The chain will not be disabled. If dropping this rule makes the chain invalid, the user should first disable the chain to ensure that it does not run.

Syntax

DBMS_SCHEDULER.DROP_CHAIN_RULE (
   chain_name              IN VARCHAR2,
   rule_name               IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

Dropping a chain rule requires alter privileges on the chain (either as the owner or as a user with ALTER privileges on the chain or the CREATE ANY JOB system privilege).

Dropping a chain rule also drops the underlying rule database object so you must have the privileges to drop this rule object. See the DBMS_RULE_ADM.DROP_RULE procedure for more information.

DROP_CHAIN_STEP Procedure

This procedure drops a chain step. If this chain step is still used in the chain rules, the chain will be disabled.

Syntax

DBMS_SCHEDULER.DROP_CHAIN_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

Dropping a chain step requires ALTER privileges on the chain (either as the owner or as a user with ALTER privileges on the chain or the CREATE ANY JOB system privilege).

DROP_CREDENTIAL Procedure

This procedure drops a credential.

Syntax

DBMS_SCHEDULER.DROP_CREDENTIAL (
   credential_name         IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

Only the owner of a credential or a user with the CREATE ANY JOB system privilege may drop the credential.

Running jobs that point to the credential are not affected by this procedure and are allowed to continue.

DROP_DATABASE_DESTINATION Procedure

This procedure drops one or more database destinations.

Syntax

DBMS_SCHEDULER.DROP_DATABASE_DESTINATION (
   destination_name        IN VARCHAR2);

Parameters

Usage Notes

Only the owner or a user with the CREATE ANY JOB system privilege may drop the database destination.

When a database destination is dropped:

• All job instances that refer to the destination in the *_SCHEDULER_JOB_DESTS views are also dropped.

• Jobs running against the destination are stopped.

• Members of database destination groups that refer to the destination are removed from the group.

DROP_FILE_WATCHER Procedure

This procedure drops one or more file watchers.

Syntax

DBMS_SCHEDULER.DROP_FILE_WATCHER (
   file_watcher_name       IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Usage Notes

Only the owner of a file watcher or a user with the CREATE ANY JOB system privilege may drop the file watcher.

Running jobs that point to the file watcher are not affected by this procedure and are allowed to continue.

DROP_GROUP Procedure

This procedure drops one or more groups.

Syntax

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

Parameters

Usage Notes

Only the owner or a user with the CREATE ANY JOB system privilege may drop a group. You must have the MANAGE SCHEDULER privilege to drop a group of type WINDOW.

DROP_JOB Procedure

This procedure drops one or more jobs or all jobs in one or more job classes. 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,
   defer                   IN BOOLEAN DEFAULT FALSE,
   commit_semantics        IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Usage Notes

If both force and defer are set to FALSE and a job is running at the time of the call, the attempt to drop that job fails. The entire call to DROP_JOB may then fail, depending on the setting of commit_semantics.

Setting both force and defer to TRUE results in an error.

Dropping a job requires ALTER privileges on the job either as the owner of the job or as a user with the ALTER object privilege on the job or the CREATE ANY JOB system privilege.

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

Usage Notes

Dropping a job class requires the MANAGE SCHEDULER system 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

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 Procedure

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.

Syntax

Drops a program argument by position:

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

Drops a program argument by name:

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

Parameters

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 drop 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

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. The window is removed from any groups that reference it.

Syntax

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

Parameters

Usage Notes

Dropping a window requires the MANAGE SCHEDULER privilege.

DROP_WINDOW_GROUP Procedure

*** Deprecated in Oracle Database 11g Release 2 (11.2). Use DROP_GROUP instead.

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

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 the 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, chain, window, database destination, external destination, file watcher, or group. When an object is enabled, its enabled attribute is set to TRUE. By default, jobs, chains, and programs are created disabled and database destinations, external destinations, file watchers, windows, and groups are created enabled.

If a job was disabled and you enable it, the Scheduler begins to automatically run the job according to its schedule. Enabling a disabled job also resets the job RUN_COUNT, FAILURE_COUNT and RETRY_COUNT columns in the *_SCHEDULER_JOBS data dictionary views.

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,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Usage Notes

Window names must be preceded by SYS.

To run ENABLE for a window or group of type WINDOW, you must have the MANAGE SCHEDULER privilege. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

To enable a file watcher, the file watcher owner must have the EXECUTE privilege on the designated credential.

You can use ENABLE with any schema except the SYS schema.

END_DETACHED_JOB_RUN Procedure

This procedure ends a detached job run. A detached job points to a detached program, which is a program with the detached attribute set to TRUE. A detached job run does not end until this procedure or the STOP_JOB Procedure is called.

Syntax

DBMS_SCHEDULER.END_DETACHED_JOB_RUN (
   job_name          IN VARCHAR2,
   error_number      IN PLS_INTEGER DEFAULT 0,
   additional_info   IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

This procedure requires that you either own the job or have ALTER privileges on it. You can also end any detached job run if you have the CREATE ANY JOB privilege.

EVALUATE_CALENDAR_STRING Procedure

You can define repeat intervals of jobs, windows or schedules using the Scheduler calendaring syntax. This procedure evaluates the calendar expression and tells you the next execution date and time of a job or window. This is very useful for testing the correct definition of the calendar string without actually scheduling the job or window.

This procedure can also 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.

See the calendaring syntax described in "Operational Notes".

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

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.

Usage Notes

No specific Scheduler privileges are required.

EVALUATE_RUNNING_CHAIN Procedure

This procedure forces reevaluation of the rules of a running chain to trigger any rules for which the conditions have been satisfied. The job passed as an argument must point to a chain and must be running. If the job is not running, an error is thrown. (RUN_JOB can be used to start the job.)

If any of the steps of the chain are themselves running chains, another EVALUATE_RUNNING_CHAIN is performed on each of the nested running chains.

Syntax

DBMS_SCHEDULER.EVALUATE_RUNNING_CHAIN (
   job_name              IN VARCHAR2);

Parameters

Usage Notes

Running EVALUATE_RUNNING_CHAIN on a job requires alter privileges on the job (either as the owner, or as a user with ALTER privileges on the job or the CREATE ANY JOB system privilege).

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

Usage Notes

If the prefix is explicitly set to NULL, the name is 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 never returns the same job name twice, there is a small chance that the returned name matches an already existing database object.

No specific Scheduler privileges are required to use this function.

GET_AGENT_INFO Function

This function can return job information specific to an agent, such as how many are running and so on, depending on the attribute selected.

Syntax

DBMS_SCHEDULER.GET_AGENT_INFO (
   agent_name        IN VARCHAR2,
   attribute         IN VARCHAR2) RETURN VARCHAR2;

Parameters

Usage Notes

This function returns the same information as the schagent utility status option.

GET_AGENT_VERSION Function

This function returns the version string of a Scheduler agent that is registered with the database and is currently running. GET_AGENT_VERSION throws an error if the agent is not registered with the database or if the agent is not currently running.

Syntax

DBMS_SCHEDULER.GET_AGENT_VERSION (
   agent_host        IN VARCHAR2) RETURN VARCHAR2;
   

Parameters

Usage Notes

This function requires the CREATE EXTERNAL JOB system privilege.

GET_ATTRIBUTE Procedure

This procedure retrieves the value of an attribute of a Scheduler object. It is overloaded to retrieve values of various types.

Syntax

DBMS_SCHEDULER.GET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          OUT {VARCHAR2|PLS_INTEGER|BOOLEAN|DATE|TIMESTAMP|
                        TIMESTAMP WITH TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE|
                        INTERVAL DAY TO SECOND});

DBMS_SCHEDULER.GET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          OUT VARCHAR2,
   value2         OUT VARCHAR2);

Parameters

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 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.

See the SET_ATTRIBUTE Procedure for tables of attribute values that you can retrieve for the various Scheduler object types.

GET_FILE Procedure

This procedure retrieves a file from the operating system file system of a specified host. The file is copied to a destination, or its contents are returned in a procedure output parameter.

You can also use this procedure to retrieve the standard output or error text for a run of an external job that has an associated credential.

This procedures differs from the equivalent UTL_FILE procedure in that it uses a credential and can retrieve files from remote hosts that have only a Scheduler agent (and not an Oracle database) installed.

Syntax

DBMS_SCHEDULER.GET_FILE (
   source_file                  IN VARCHAR2,
   source_host                  IN VARCHAR2,
   credential_name              IN VARCHAR2,
   file_contents                IN OUT NOCOPY {BLOB|CLOB});

DBMS_SCHEDULER.GET_FILE (
   source_file                  IN VARCHAR2,
   source_host                  IN VARCHAR2,
   credential_name              IN VARCHAR2,
   destination_file_name        IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   destination_permissions      IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The caller must have the CREATE EXTERNAL JOB system privilege and have EXECUTE privileges on the credential.

GET_SCHEDULER_ATTRIBUTE Procedure

This procedure retrieves the value of a Scheduler attribute.

Syntax

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

Parameters

Usage Notes

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

Table 128-67 lists the Scheduler attributes that you can retrieve. For more detail on these attributes, see Table 128-97 and the section "Configuring the Scheduler" in Oracle Database Administrator's Guide.

OPEN_WINDOW Procedure

This procedure opens a window independent of its schedule. This window opens and the resource plan associated with it takes 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

Usage Notes

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 is 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.

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

If a window fails to switch resource plans because the designated resource plan no longer exists or because resource plan switching by windows is disabled (for example, by using the ALTER SYSTEM statement with the force option), the failure to switch resource plans is recorded in the window log.

Opening a window requires the MANAGE SCHEDULER privilege.

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.

Rows in the job log table pertaining to the steps of a chain are purged only when the entry for the main chain job is purged (either manually or automatically).

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

Usage Notes

This procedure requires the MANAGE SCHEDULER privilege.

Examples

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

DBMS_SCHEDULER.PURGE_LOG();

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

DBMS_SCHEDULER.PURGE_LOG(5, 'window_log');

The following purges 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 older than 1 day:

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

PUT_FILE Procedure

This procedure saves a file to the operating system file system of a specified remote host or of the local computer. It differs from the equivalent UTL_FILE procedure in that it uses a credential and can save files to a remote host that has only a Scheduler agent (and not an Oracle Database) installed.

Syntax

DBMS_SCHEDULER.PUT_FILE (
   destination_file         IN VARCHAR2,
   destination_host         IN VARCHAR2,
   credential_name          IN VARCHAR2,
   file_contents            IN {BLOB|CLOB},
   destination_permissions  IN VARCHAR2 DEFAULT NULL);

DBMS_SCHEDULER.PUT_FILE (
   destination_file         IN VARCHAR2,
   destination_host         IN VARCHAR2,
   credential_name          IN VARCHAR2,
   source_file_name         IN VARCHAR2,
   source_directory_object  IN VARCHAR2,
   destination_permissions  IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The caller must have the CREATE EXTERNAL JOB system privilege and have EXECUTE privileges on the credential.

REMOVE_EVENT_QUEUE_SUBSCRIBER Procedure

This procedure unsubscribes a user from the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE.

Syntax

DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER (
   subscriber_name         IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

After the agent is unsubscribed, it is deleted. If the agent does not exist or is not currently subscribed to the Scheduler event queue, an error is raised.

REMOVE_GROUP_MEMBER Procedure

This procedure removes one or more members from an existing group.

Syntax

DBMS_SCHEDULER.REMOVE_GROUP_MEMBER (
   group_name              IN VARCHAR2,
   member                  IN VARCHAR2);

Parameters

Usage Notes

The following users may remove members from a group:

• The group owner

• A user that has been granted the ALTER object privilege on the group

• A user with the CREATE ANY JOB system privilege

You must have the MANAGE SCHEDULER privilege to remove a member from a group of type WINDOW.

REMOVE_JOB_EMAIL_NOTIFICATION Procedure

This procedure removes e-mail notifications for a job. You can remove all e-mail notifications or remove notifications only for specified recipients or specified events.

Syntax

DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
    job_name             IN VARCHAR2,
    recipients           IN VARCHAR2 DEFAULT NULL,
    events               IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

When you specify multiple recipients and multiple events, the notification for each specified event is removed for each specified recipient. The procedure ignores any recipients or events that are specified but that were not previously added.

If recipients is NULL, e-mail notifications for the specified events are removed for all existing recipients. If events is NULL, notifications for all events are removed for the specified recipients. If both recipients and events are NULL, all e-mail notifications are removed for the job.

For example, if recipients is 'jsmith@example.com,rjones@example.com' and events is 'JOB_FAILED,JOB_BROKEN', then notifications for both the JOB_FAILED and JOB_BROKEN events are removed for both jsmith and rjones. If recipients is NULL, then notifications for both the JOB_FAILED and JOB_BROKEN events are removed for jsmith, rjones, and any other previously defined recipients for these events.

To call this procedure, you must be the job owner or a user with the CREATE ANY JOB system privilege or ALTER object privilege on the job.

REMOVE_WINDOW_GROUP_MEMBER Procedure

*** Deprecated in Oracle Database 11g Release 2 (11.2). Use REMOVE_GROUP_MEMBER instead.

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

Usage Notes

If any of the windows specified is 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 any running jobs that has the window as its schedule because the jobs only stop when a window closes.

RESET_JOB_ARGUMENT_VALUE Procedure

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

Usage Notes

If the corresponding program argument has no default value, the job is 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 reset a job argument value if you have the CREATE ANY JOB privilege.

RESET_JOB_ARGUMENT_VALUE only supports arguments of SQL type. Therefore, argument values that are not of SQL type, such as booleans, are not supported as program or job arguments.

RUN_CHAIN Procedure

This procedure immediately runs a chain or part of a chain by creating a run-once job with the job name given. If no job_name is given, one is generated of the form RUN_CHAIN$_chainnameN, where chainname is the first 8 characters of the chain name and N is an integer.If a list of start steps is given, only those steps are started when the chain begins running. Steps not in the list that would normally have started are skipped and paused (so that they or the steps after them do not run). If start_steps is NULL, then the chain starts normally—that is, it performs an initial evaluation to see which steps to start running).

If a list of initial step states is given, the newly created chain job sets every listed step to the state specified for that step before evaluating the chain rules to see which steps to start. (Steps in the list are not started.)

Syntax

Runs a chain, with a list of start steps.

DBMS_SCHEDULER.RUN_CHAIN (
   chain_name                IN VARCHAR2,
   start_steps               IN VARCHAR2,
   job_name                  IN VARCHAR2 DEFAULT NULL);

Runs a chain, with a list of initial step states.

DBMS_SCHEDULER.RUN_CHAIN (
   chain_name               IN VARCHAR2, 
   step_state_list          IN SYS.SCHEDULER$_STEP_TYPE_LIST, 
   job_name                 IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Running a chain requires CREATE JOB if the job is being created in the user's schema, or CREATE ANY JOB otherwise. In addition, the owner of the job being created needs execute privileges on the chain (as the owner of the chain, or as a user with the EXECUTE privilege on the chain or the EXECUTE ANY PROGRAM system privilege).

Examples

The following example illustrates how to start a chain in the middle by providing the initial state of some chain steps.

declare
  initial_step_states sys.scheduler$_step_type_list;
begin
  initial_step_states := sys.scheduler$_step_type_list(
    sys.scheduler$_step_type('step1', 'SUCCEEDED'),
    sys.scheduler$_step_type('step2', 'FAILED 27486'),
    sys.scheduler$_step_type('step3', 'SUCCEEDED'),
    sys.scheduler$_step_type('step5', 'SUCCEEDED'));
  dbms_scheduler.run_chain('my_chain', initial_step_states);
end;
/

RUN_JOB Procedure

This procedure runs a job immediately.

If a job is enabled, the Scheduler runs it automatically. It is not necessary to call RUN_JOB to run a job according to its schedule. Use RUN_JOB to run a job outside of its normal schedule.

Syntax

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

Parameters

Usage Notes

The job does not have to be enabled. If the job is disabled, the following validity checks are performed before running it:

• The job points to a valid job class.The job owner has EXECUTE privileges on the job class.If a program or chain is referenced, the program/chain exists.If a program or chain is referenced, the job owner has privileges to execute the program/chain.All argument values have been set (or have defaults).The job owner has the CREATE EXTERNAL JOB privilege if this is an external job.

A TRUE value for use_current_session is not permitted for the following types of jobs:

• Jobs that specify a destination or destination group in the destination_name attribute

• Jobs that point to chains (chain jobs)

• Jobs that make use of detached programs (detached jobs).

When use_current_session is TRUE, the call to RUN_JOB blocks until the job completes. Any errors that occur during the execution of the job are returned as errors to the RUN_JOB procedure. When use_current_session is FALSE, RUN_JOB returns immediately and the job is picked up by the job 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 own the job or have ALTER privileges on that job. You can also run a job if you have the CREATE ANY JOB privilege.

Example

The following is an example of using RUN_JOB.

BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'EODJOB, DSS.ETLJOB',
    USE_CURRENT_SESSION => FALSE);
END;

SET_AGENT_REGISTRATION_PASS Procedure

This procedure sets the agent registration password for a database. A Scheduler agent must register with the database before the database can submit jobs to the agent. The agent must provide this password when registering.

Syntax

DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS (
   registration_password   IN VARCHAR2,
   expiration_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   max_uses                IN NUMBER DEFAULT NULL);

Parameters

Usage Notes

To prevent abuse, this password can be set to expire after a given date or a maximum number of successful registrations. This procedure will overwrite any password already set. This requires the MANAGE SCHEDULER system privilege.

By default, max_uses is set to NULL, which means that there is no limit to the number of successful registrations.

Oracle recommends that an agent registration password be reset after every agent registration or every known set of agent registrations. Furthermore, Oracle recommends that this password be set to NULL if no new agents are being registered.

SET_ATTRIBUTE Procedure

This procedure modifies an attribute of a Scheduler object. It is overloaded to accept values of various types. To set an attribute to NULL, use the SET_ATTRIBUTE_NULL procedure. The attributes that can be set depend on the object being altered. All object attributes can be changed, except the object name.

Syntax

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN {BOOLEAN|DATE|TIMESTAMP|
                        TIMESTAMP WITH TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE|
                        INTERVAL DAY TO SECOND});

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN VARCHAR2,
   value2         IN VARCHAR2 DEFAULT NULL);

Parameters

Table 128-80 is a directory of Scheduler object types and tables of attributes for the object types.

These object types can be viewed with Scheduler Data Dictionary Views, listed in Oracle Database Administrator's Guide .

Usage Notes

If an object is altered and it was in the enabled state, the Scheduler first disables it, then makes the change and reenables it. If any errors are encountered during the enable process, the object is not reenabled and an error is generated.

If an object is altered and it was in the disabled state, it remains disabled after it is altered.

To run SET_ATTRIBUTE for a window, a group of type WINDOW, 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 affects 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 is 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 take effect the next time the job runs. Program attributes of a job include program_name, job_action, job_type, and number_of_arguments.

If any job argument values are altered while the job is running, the new values take effect the next time the job runs.

Granting the ALTER privilege on a job lets 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 does not allow a user to use a PL/SQL expression to specify the schedule for a job.

Oracle recommends that you not 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 job views.

Program

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

Schedule

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

File Watcher

If a file watcher is altered, any currently running event-based jobs started by the file arrival event are not affected. On the local system, the new file watcher attributes take effect the next time that the file watcher checks for the arrival of the file (every ten minutes by default). On remote systems, there may be an additional delay before the new file watcher attributes take effect.

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.

If there is no current resource plan, when a window with a designated resource plan opens, the Resource Manager activates with that plan.

Job Attribute Values

Table 128-81 lists attribute values for jobs.

hostname:port

The following event types are valid values for the raise_events attribute in Table 128-81, "Job Attribute Values".