140 DBMS_SCHEDULER

FREQ

This specifies the type of recurrence. It must be specified. The possible predefined frequency values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY. Alternatively, specifies an existing schedule to use as a user-defined frequency.

INTERVAL

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

BYMONTH

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

BYWEEKNO

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

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

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

byweekno is only valid for YEARLY.

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

BYYEARDAY

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

BYDATE

This specifies a list of dates, where each date is of the form [YYYY]MMDD. A list of consecutive dates can be generated by using the SPAN modifier, and a date can be adjusted with the OFFSET modifier. An example of a simple BYDATE clause follows:

BYDATE=0115,0315,0615,0915,1215,20060115

The following SPAN example is equivalent to BYDATE=0110,0111,0112,0113,0114, which is a span of 5 days starting at 1/10:

BYDATE=0110+SPAN:5D

The plus sign in front of the SPAN keyword indicates a span starting at the supplied date. The minus sign indicates a span ending at the supplied date, and the "^" sign indicates a span of n days or weeks centered around the supplied date. If n is an even number, it is adjusted up to the next odd number.

Offsets adjust the supplied date by adding or subtracting n days or weeks. BYDATE=0205-OFFSET:2W is equivalent to BYDATE=0205-14D (the OFFSET: keyword is optional), which is also equivalent to BYDATE=0122.

BYMONTHDAY

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

BYDAY

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

BYHOUR

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

BYMINUTE

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

BYSECOND

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

BYSETPOS

This selects one or more items, by position, in the list of timestamps that result after the whole calendaring expression is evaluated. It is useful for requirements such as running a job on the last workday of the month. Rather than attempting to express this with the other BY clauses, you can code the calendaring expression to evaluate to a list of every workday of the month, and then add the BYSETPOS clause to select only the last item of that list. Assuming that workdays are Monday through Friday, the syntax would then be:

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

Valid values are 1 through 9999. A negative number selects an item from the end of the list (-1 is the last item, -2 is the next to last item, and so on) and a positive number selects from the front of the list. The BYSETPOS clause is always evaluated last. BYSETPOS is only supported with the MONTHLY and YEARLY frequencies.

The BYSETPOS clause is applied to the list of timestamps once per frequency period. For example, when the frequency is defined as MONTHLY, the Scheduler determines all valid timestamps for the month, orders that list, and then applies the BYSETPOS clause. The Scheduler then moves on to the next month and repeats the procedure. Assuming a start date of Jun 10, 2004, the example evaluates to: Jun 30, Jul 30, Aug 31, Sep 30, Oct 29, and so on.

INCLUDE

This includes one or more named schedules in the calendaring expression. That is, the set of timestamps defined by each included named schedule is added to the results of the calendaring expression. If an identical timestamp is contributed by both an included schedule and the calendaring expression, it is included in the resulting set of timestamps only once. The named schedules must have been defined with the CREATE_SCHEDULE procedure.

This clause only works on a full day and therefore cannot be used with BYHOUR, BYMIN, and BYSECOND.

EXCLUDE

This excludes one or more named schedules from the calendaring expression. That is, the set of timestamps defined by each excluded named schedule is removed from the results of the calendaring expression. The named schedules must have been defined with the CREATE_SCHEDULE procedure.

This clause only works on a full day and therefore cannot be used with BYHOUR, BYMIN, and BYSECOND.

INTERSECT

This specifies an intersection between the calendaring expression results and the set of timestamps defined by one or more named schedules. Only the timestamps that appear both in the calendaring expression and in one of the named schedules are included in the resulting set of timestamps.

For example, assume that the named schedule last_sat indicates the last Saturday in every month, and that for the year 2005, the only months where the last day of the month is also a Saturday are April and December. Assume also that the named schedule end_qtr indicates the last day of each quarter in 2005:

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

These calendaring expressions result in the dates that follow:

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

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

In this example, the terms FREQ=MONTHLY; BYMONTHDAY=-1 indicate the last day of each month.

This clause only works on a full day and therefore cannot be used with BYHOUR, BYMIN, and BYSECOND.

PERIODS

This identifies the number of periods that together form one cycle of a user-defined frequency. It is used in the repeat_interval expression of the schedule that defines the user-defined frequency. It is mandatory when the repeat_interval expression in the main schedule contains a BYPERIOD clause. The following example defines the quarters of a fiscal year.

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

BYPERIOD

This selects periods from a user-defined frequency. For example, if a main schedule names a user-defined frequency schedule that defines the fiscal quarters shown in the previous example, the clause BYPERIOD=2,4 in the main schedule selects the 2nd and 4th fiscal quarters.

arg_position

Position of the argument

arg_text_value

Value of the argument if the type is VARCHAR2

arg_anydata_value

Value of the argument if the type is AnyData

arg_operation

Type of the operation:

• SET

• RESET

job_name

Name of the job

job_class

Name of the job class

job_style

Style of the job:

• REGULAR

• LIGHTWEIGHT

program_name

Name of the program that the job runs

job_action

Inline action of the job. This is either the code for an anonymous PL/SQL block or the name of a stored procedure, external executable, or chain.

job_type

Job action type ('PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', 'CHAIN', 'EXTERNAL_SCRIPT', 'SQL_SCRIPT', and 'BACKUP_SCRIPT')

schedule_name

Name of the schedule that specifies when the job has to execute

repeat_interval

Inline time-based schedule

schedule_limit

Maximum delay time between scheduled and actual job start before a job run is canceled

start_date

Start date and time of the job

end_date

End date and time of the job

event_condition

Event condition for event-based jobs

queue_spec

File watcher name or queue specification for event-based jobs

number_of_arguments

Number of job arguments

arguments

Array of job arguments

job priority

Job priority

job_weight

*** Deprecated in Oracle Database 11gR2. Do not change the value of this attribute from the default, which is 1.

Weight of the job for parallel execution.

max_run_duration

Maximum run duration of the job

max_runs

Maximum number of runs before the job is marked as completed

max_failures

Maximum number of failures tolerated before the job is marked as broken

logging_level

Job logging level

restartable

Indicates whether the job is restartable (TRUE) or not (FALSE)

stop_on_window_close

Indicates whether the job is stopped when the window that it runs in ends (TRUE) or not (FALSE). Equivalent to the stop_on_window_close job attribute described in the SET_ATTRIBUTE Procedure.

raise_events

State changes that raise events

comments

Comments on the job

auto_drop

If TRUE (the default), indicates that the job should be dropped once completed

enabled

Indicates whether the job should be enabled immediately after creating it (TRUE) or not (FALSE)

follow_default_timezone

If TRUE and if the job start_date is null, then when the default_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

parallel_instances

For event-based jobs only.

If TRUE, on the arrival of the specified event, the Scheduler creates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel.

If FALSE, then an event is discarded if it is raised while the job that handles it is already running,

aq_job

For internal use only

instance_id

The instance ID of the instance that the job must run on

credential_name

The credential to use for a single destination or the default credential for a group of destinations

destination

The name of a single external destination or database destination, or a group name of type external destination or database destination

database_role

In an Oracle Data Guard environment, the database role ('PRIMARY' or 'LOGICAL STANDBY') for which the job runs

allow_runs_in_restricted_mode

If TRUE, the job is permitted to run when the database is in restricted mode, provided that the job owner is permitted to log in during this mode

restart_on_recovery

If set to TRUE for a job and the job is stopped by a database shutdown, then the job is restarted when the database is recovered.

If set to FALSE, and the job is stopped by a database shutdown, then the job is marked as stopped when the database is recovered.

restart_on_failure

If set to TRUE for a job and the job fails due to an application error, then the job is retried using the normal Scheduler retry mechanism (after 1 second, after 10 seconds, after 100 seconds, and so on, up to a maximum of 6 times). If all 6 retries fail (after about 30 hours), then the job is marked FAILED.

If set to FALSE (the default), a failed job is immediately marked FAILED.

job_name

Name of the job

attr_name

Name of the attribute

char_value

Value of the argument if the type is VARCHAR2

char_value2

Second VARCHAR2 attribute value

args_value

Value of the argument if the type is a JOBARG array

num_value

Value of the argument if the type is NUMBER

timestamp_value

Value of the argument if the type is TIMESTAMP WITH TIME ZONE

interval_value

Value of the argument if the type is INTERVAL DAY TO SECOND

step_name

Name of the step

step_type

State of the step

event_type

One of "JOB_STARTED", "JOB_SUCCEEDED", "JOB_FAILED", "JOB_BROKEN", "JOB_COMPLETED", "JOB_STOPPED", "JOB_SCH_LIM_REACHED", "JOB_DISABLED", "JOB_CHAIN_STALLED", "JOB_OVER_MAX_DUR".

For descriptions of these event types, see Table 140-78, "Event Types Raised by the Scheduler".

object_owner

Owner of the job that raised the event

object_name

Name of the job that raised the event

event_timestamp

Time at which the event occurred

error_code

Applicable only when an error is thrown during job execution. Contains the top-level error code.

error_msg

Applicable only when an error is thrown during job execution. Contains the entire error stack.

event_status

Adds further qualification to the event type. If event_type is "JOB_STARTED," status 1 indicates that it is a normal start, and status 2 indicates that it is a retry.

If event_type is "JOB_FAILED," status 4 indicates that it was a failure due to an error that was thrown during job execution, and status 8 indicates that it was an abnormal termination of some kind.

If event_type is "JOB_STOPPED," status 16 indicates that it was a normal stop, and status 32 indicates that it was a stop with the FORCE option set to TRUE.

log_id

Points to the ID in the scheduler job log from which additional information can be obtained. Note that there need not always be a log entry corresponding to an event. In such cases, log_id is NULL.

run_count

Run count for the job when the event was raised.

failure_count

Failure count for the job when the event was raised.

retry_count

Retry count for the job when the event was raised.

spare1 – spare8

Not currently in use.

destination

Destination at which the file was found, expressed as a host name or IP address.

directory_path

Absolute path of directory in which the file was found.

actual_file_name

Actual name of the file that was found. If the file name specified in the file watcher did not contain wildcards, then this is the same as the name specified in the file watcher.

file_size

Size of the file that was found, in bytes.

file_timestamp

Timestamp assigned to the file when the file watcher considered the file found, based on the minimum file size and steady state duration attributes.

ts_ms_from_epoch

For internal use only.

matching_requests

List of matching requests. This is a TABLE of type objects SCHEDULER_FILEWATCHER_REQUEST. Each matching request corresponds to a file watcher whose destination, directory_path, and file_name attributes matched the arrived file. See "SCHEDULER_FILEWATCHER_REQUEST Object Type".

owner

Owner of the matched file watcher.

name

Name of the matched file watcher.

requested_path_name

Value of the directory_path attribute of the matched file watcher.

requested_file_name

Value of the file_name attribute of the matched file watcher.

credential_owner

Owner of the credential referenced by the matched file watcher.

credential_name

Name of the credential referenced by the matched file watcher.

min_file_size

Value of the min_file_size attribute of the matched file watcher.

steady_state_dur

Value of the steady_state_duration attribute of the matched file watcher.

subscriber_name

Name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. If NULL, an agent is created and assigned the user name of the calling user.

group_name

The name of the group.

member

A comma-separated list of members to add to the group. Members must match the group type. A group of the same type can be a member. The Scheduler immediately expands the included group name into its list of members.

An error is returned if any of the members do not exist. A member that is already in the group is skipped, and no error is generated.

The keyword LOCAL can be included as a member for database destination or external destination groups. See the "CREATE_GROUP Procedure" for information about this keyword.

job_name

Name of the job that e-mail notifications are added for. Cannot be NULL.

recipients

Comma-separated list of e-mail addresses to send notifications to. E-mail notifications for all listed events are sent to all recipients. Cannot be NULL.

sender

e-mail address to use as the sender address (the From: address) in the e-mail header. If NULL or omitted, the e-mail address specified in the Scheduler attribute email_sender is used. See Oracle Database Administrator's Guide for more information on this Scheduler attribute.

subject

The subject to use in the e-mail header. Table 140-15 describes the variables that you can include within this parameter. The Scheduler assigns values to these variables before sending the notification. If subject is omitted, the default subject is used. The default subject is the following text, where text enclosed in the '%' character represents a variable:

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

body

The body of the e-mail message. Table 140-15 describes the variables that you can include within this parameter. The Scheduler assigns values to these variables before sending the notification. If body is omitted, the default body is used. The default body is the following text, where text enclosed in the '%' character represents a variable:

'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%'

events

Comma-separate list of job state events to send e-mail notifications for. Cannot be NULL. A notification is sent to all recipients if any of the listed events is raised. Table 140-78 lists the valid events for this parameter. If events is omitted, notifications are sent for the following default events:

JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR

filter_condition

Used to filter events to send e-mail notifications for. If NULL, all occurrences of the specified events cause e-mail notifications to be sent. filter_condition must be a boolean SQL WHERE clause that may refer to the :event bind variable. This bind variable is automatically bound to an object of type SCHEDULER$_EVENT_INFO that represents the raised event.

For example, to send an e-mail notification only when the error number in an event is 600 or 700, use the following filter_condition:

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

See "SCHEDULER$_EVENT_INFO Object Type".

%job_owner%

Schema in which job was created

%job_name%

Name of the job that e-mail notifications are added for

%job_subname%

Present for event-based jobs with the parallel_instances attribute set and for chain steps

%event_type%

Valid values are listed in Table 140-78

%event_timestamp%

Time at which the event occurred

%log_id%

Refers to the LOG_ID column in views *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS

%error_code%

Number of the error code.

%error_message%

The text of the error message

%run_count%

Run count for the job when the event was raised

%failure_count%

Failure count for the job when the event was raised

%retry_count%

Retry count for the job when the event was raised

chain_name

The name of the chain to alter

step_name

The name of the step or a comma-separated list of steps to alter. This cannot be NULL.

attribute

The attribute of the steps to change. Must be one of the following:

• 'PAUSE'

  If set to TRUE for a step, after the step has run, its state changes to PAUSED (and the completed attribute remains FALSE).

  If PAUSE is reset to FALSE for a paused chain step (using ALTER_RUNNING_CHAIN), the state is set to its completion state (SUCCEEDED, FAILED, or STOPPED) and the completed attribute is set to TRUE.

  Setting PAUSE has no effect on steps that have already run. This allows execution of a chain to be suspended after the execution of certain steps.

• 'PAUSED_BEFORE'

  If set to TRUE for a step and if any of the rule conditions that start the step are true, then its state changes to PAUSED and the step does not run.

  If PAUSE_BEFORE is reset to FALSE for a chain step that has paused before starting (using ALTER_RUNNING_CHAIN), then the step starts running if any of the rule conditions that start the step are true.

  Setting PAUSE_BEFORE has no effect on steps that are running or have already run. This allows execution of a chain to be suspended before the execution of certain steps.

• 'SKIP'

  If set to TRUE for a step, when the step condition is met, instead of being run, the step is treated as if it has immediately succeeded. Setting SKIP to TRUE has no effect for a step that is running, scheduled to run after a delay, or has already run. If SKIP is set TRUE for a step that PAUSE is also set for, when the step condition is met, the step immediately changes to state PAUSED.

• 'RESTART_ON_FAILURE'

  If set to TRUE for a step and the step fails due to an application error, then the step is retried using the normal Scheduler retry mechanism (after 1 second, after 10 seconds, after 100 seconds, and so on, up to a maximum of 6 times). If all 6 retries fail (after about 30 hours), then the chain step is marked FAILED.

  If set to FALSE (the default), a failed chain step is immediately marked FAILED.

• 'RESTART_ON_RECOVERY'

  If set to TRUE for a step and the step is stopped by a database shutdown, then the step is restarted when the database is recovered.

  If set to FALSE, and the step is stopped by a database shutdown, then the step is marked as stopped when the database is recovered and the chain continues.

• 'DESTINATION_NAME'

  The name of an existing database destination or external destination. You can view external destination names in the view ALL_SCHEDULER_EXTERNAL_DESTS, and database destination names in the views *_SCHEDULER_DB_DESTS. You cannot specify a destination group for this attribute. This parameter is NULL by default.

• 'CREDENTIAL_NAME'

  The credential to use when running this step. NULL by default.

value

The value to set for the attribute (for a boolean attribute).

char_value

The value to set for the attribute (for a character attribute).

job_name

The name of the job that is running the chain

step_name

The name of the step or a comma-separated list of steps to alter. If this is set to NULL and attribute is PAUSE or SKIP, then all steps of the running chain are altered.

attribute

The attribute of the steps to change. Valid values are:

• 'PAUSE'

  If the PAUSE attribute is set TRUE for a step, then after the step runs, its state changes to PAUSED (and the completed attribute remains false).

  If PAUSE is reset to FALSE for a paused chain step (using ALTER_RUNNING_CHAIN), the state is set to completion (SUCCEEDED, FAILED, or STOPPED) and the completed attribute is set to TRUE. Setting PAUSE has no effect on steps that have already run. This allows execution of a chain to be suspended after the execution of certain steps. If step_name is set to NULL, PAUSE is set to TRUE for all steps of this running chain.

• 'PAUSE_BEFORE'

  If set to TRUE for a step that has not yet run and if any of the rule conditions that start the step are true, then its state changes to PAUSED and the step does not run.

  If PAUSE_BEFORE is reset to FALSE for a chain step that has paused before starting, then the step starts running if any of the rule conditions that start the step are true.

  Setting PAUSE_BEFORE has no effect on steps that are running or have already run. This allows execution of a chain to be suspended before the execution of certain steps.

  If step_name is set to NULL, then PAUSE_BEFORE is set to the specified value for all steps of this running chain.

attribute CONTINUED

• 'SKIP'

  If the SKIP attribute is set to TRUE for a step, when the step condition is met, instead of being run, the step is treated as if it has immediately succeeded. Setting SKIP to TRUE has no effect for a step that is running, scheduled to run after a delay, or has already run.

  If step_name is set to NULL, SKIP is set TRUE for all steps of this running chain. If SKIP is set TRUE for a step that PAUSE is also set for, when the step condition is met the step immediately changes to state PAUSED.

• 'RESTART_ON_FAILURE'

  If set to TRUE for a step and the step fails due to an application error, then the step is retried using the normal Scheduler retry mechanism (after 1 second, after 10 seconds, after 100 seconds, and so on, up to a maximum of 6 times). If all 6 retries fail (after about 30 hours), then the chain step is marked FAILED.

  If set to FALSE (the default), a failed chain step is immediately marked FAILED.

• 'RESTART_ON_RECOVERY'

  If the RESTART_ON_RECOVERY attribute is set to TRUE for a step, then if the step is stopped by a database shutdown, it is restarted when the database is recovered.

  If set to FALSE, then if the step is stopped by a database shutdown, the step is marked as stopped when the database is recovered and the chain continues.

• 'STATE'

  This changes the state of the steps. The state can only be changed if the step is not running. The state can only be changed to one of the following:

  'NOT_STARTED', 'SUCCEEDED', 'FAILED error_code'
  

  If the state is being changed to FAILED, an error code must be included (this must be a positive integer).

value

The value to set for the attribute. Valid values are: TRUE, FALSE, 'NOT_STARTED', 'SUCCEEDED', or 'FAILED error_code'

window_name

The name of the window

old_job

The name of the existing job

new_job

The name of the new job

chain_name

The name to assign to the new chain, which can optionally be qualified with a schema. This must be unique in the SQL namespace, therefore, there cannot already be a table or other object with this name and schema.

rule_set_name

In the normal case, no rule set should be passed in. The Scheduler automatically creates a rule set and associated empty evaluation context. You then use DEFINE_CHAIN_RULE to add rules and DROP_CHAIN_RULE to remove them.

Advanced users can create a rule set that describes their chain dependencies and pass it in here. This allows greater flexibility in defining rules. For example, conditions can refer to external variables, and tables can be exposed through the evaluation context. If you pass in a rule set, you must ensure that it is in the format of a chain rule set. (For example, all steps must be listed as variables in the evaluation context). If no rule set is passed in, the rule set created is of the form SCHED_RULESET${N} and the evaluation context created is of the form SCHED_EVCTX${N}

See Oracle Streams Concepts and Administration for information on rules and rule sets.

evaluation_interval

If this is NULL, reevaluation of the rules of a running chain are performed only when the job starts and when a step completes. A non-NULL value causes rule evaluations to also occur periodically at the specified interval. Because evaluation may be CPU-intensive, this should be conservatively set to the highest possible value or left at NULL if possible. evaluation_interval cannot be less than a minute or greater than a day.

comments

An optional comment describing the purpose of the chain

credential_name

The name to assign to the credential. It can optionally be prefixed with a schema name. It cannot be set to NULL. It is converted to uppercase unless enclosed in double quotation marks.

username

The user name for logging into to the host operating system or remote Oracle database. This cannot be set to NULL and is case-sensitive. It cannot contain double quotes or spaces. Maximum length is 64.

password

The password for the user name. This cannot be set to NULL and is case sensitive. The password is stored obfuscated and is not displayed in the Scheduler dictionary views. Maximum length is 128.

database_role

The value of the database_role attribute is used as the system privilege for logging into a remote database to run a remote database job.

Valid values are: SYSDBA and SYSOPER

windows_domain

For a Windows remote executable target, this is the domain that the specified user belongs to. The domain is converted to uppercase automatically. Maximum length is 64.

comments

A text string that can be used to describe the credential. Scheduler does not use this parameter. Maximum length is 240.

destination_name

The name to assign to the database destination. It can optionally be prefixed with a schema name. Cannot be NULL. It is converted to uppercase unless enclosed in double quotation marks.

agent

The external destination name of the Scheduler agent to connect. Equivalent to an agent name.

The external destination must already exist. The external destination representing an agent is created automatically on a database instance when the agent registers with that instance.

An agent's name is specified in its agent configuration file. If it is not specified, it defaults to the first part (before the first period) of the name of the host it resides on.

tns_name

An Oracle Net connect identifier that is resolved to the Oracle database instance being connected to. The exact syntax depends on the Oracle Net configuration.The connect identifier can be a complete Oracle Net connect descriptor (network address and database service name) or a net service name, which is an alias for a connect descriptor. The alias must be resolved in the tnsnames.ora file on the local computer. The maximum size for tns_name is 2000 characters.

If tns_name is NULL, the agent connects to the default Oracle database on its host. You specify the default database by assigning values to the ORACLE_HOME and ORACLE_SID parameters in the agent configuration file, schagent.conf, located in the agent home directory.

See Oracle Database Net Services Administrator's Guide for more information on connect identifiers.

comments

A text string that describes the database destination. Scheduler does not use this argument.

file_watcher_name

The name to assign to the file watcher. The name must be unique in the SQL namespace. For example, a file watcher cannot have the same name as a table in a schema. This can optionally be prefixed with a schema name. Cannot be NULL.

directory_path

Directory in which the file is expected to arrive. The single wildcard '?' at the beginning of the path denotes the Oracle home path. For example, '?/rdbms/log' denotes the rdbms/log subdirectory of the Oracle home directory.

file_name

Name of the file to look for. Two wildcards are permitted anywhere in the file name: '?' denotes any single character, and '*' denotes zero or more characters. This attribute cannot be NULL.

credential_name

Name of a valid credential object.

The file watcher uses the credential to authenticate itself with the host operating system to access the watched-for file. The file watcher owner must have EXECUTE privileges on the credential. Cannot be NULL.

destination

Name of an external destination. You create an external destination by registering a remote Scheduler agent with the database. See the view ALL_SCHEDULER_EXTERNAL_DESTS for valid external destination names. If this parameter is NULL, the file watcher is created on the local host.

min_file_size

Minimum size in bytes that the file must be before the file watcher considers the file found. Default is 0.

steady_state_duration

Minimum time interval that the file must remain unchanged before the file watcher considers the file found. Cannot exceed one hour. If NULL, an internal value is used. The minimum value is 10 seconds. Oracle recommends similar steady_state_duration values for all file watchers for efficient file watcher job operation. Also, the repeat interval of the file watcher schedule must be equal or greater than the steady_state_duration value.

comments

Optional comment.

enabled

If TRUE (the default), the file watcher is enabled.

group_name

The name to assign to the group. It can optionally be prefixed with a schema name. It cannot be NULL. It is converted to uppercase unless enclosed in double quotation marks.

group_type

The type of members in the group. All members must be of the same type. Possible types are:

• 'DB_DEST'

  Database destination: Members are database destinations, for running remote database jobs.

• 'EXTERNAL_DEST

  External destination: Members are external destinations, for running remote external jobs.

• 'WINDOW'

  Members are Scheduler windows. You must have the MANAGE SCHEDULER privilege to create a group of this type.

Members in database destination and external destination groups have the following format:

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

where:

• credential is the name of an existing credential.

• destination is the name of an existing database destination or external destination.

The credential portion of a destination member is optional. If omitted, the job using this destination member uses its default credential.

Members in window groups are window names. Because all Scheduler windows reside in the SYS schema, you do not specify a schema name for windows.

member

Optional comma-separated list of group members. The default is NULL. If NULL, use the ADD_GROUP_MEMBER procedure to add members. You can also use ADD_GROUP_MEMBER to add additional members at a later time.

The keyword LOCAL can be used as a member in database destination groups and external destination groups.

• In database destination groups, LOCAL represents the source database on which the job is created. It cannot be preceded with a credential.

• In external destination groups, LOCAL represents the host on which the source database resides. It can be optionally preceded with a credential name. If no credential is provided, jobs that use this group as their destination must have a default credential.

comments

A text string that describes the group. Scheduler does not use this argument.

job_name

The name to assign to the job. The name must be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema. If the job being created will reside in another schema, it must be qualified with the schema name.

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

job_type

This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. See job_action in the next row for related information.

The supported values are:

• 'PLSQL_BLOCK'

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

• 'STORED_PROCEDURE'

  This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.

• 'EXECUTABLE'

  This specifies that the job is going to be run outside the database using an external executable. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

• 'CHAIN'

  This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.

• 'EXTERNAL_SCRIPT'

  This specifies that the job is an external script that uses the command shell of the computer running the job. For Windows this is cmd.exe and for UNIX based systems the sh shell, unless a different interpreter is specified by prefixing the first line of the script with #!.

• 'SQL_SCRIPT'

  This specifies that the job is a SQL*Plus script.

  The job must point to a credential that contains a valid operating system username and password. The SQL*Plus script is run by the SQL*Plus executable. The job may point to a connect credential that contains a database credential. If so, this credential is used to connect to the database before running the SQL*Plus script.

  Note that if you choose to use connect credential, you must use set_attribute to specify the Connect_Credential_Name attribute. If you do not have connect credential, you must include an explicit SQL*Plus connect statement providing a valid database userid / password.

  The job owner must have the CREATE EXTERNAL JOB system privilege.

• 'BACKUP_SCRIPT'

  This specifies that the job is an RMAN backup script.

  The script runs a connect statement that uses either a password or OS authentication before it executes any target commands. The job points to a credential that contains a valid operating system username and password. The RMAN session runs under this operating system user.

  The Scheduler uses the RMAN executable from the current Oracle home to run the script and throws an error if this is missing.

  The job owner must have the CREATE EXTERNAL JOB system privilege.

job_action

This attribute specifies the action of the job. If job_action is not specified for an inline program, then an error is generated when creating the job.

The following actions are possible:

• For a PL/SQL block:

  The action is to execute PL/SQL code. These blocks must end with a semicolon. For example, my_proc(); or BEGIN my_proc(); END; or DECLARE arg pls_integer:= 10; BEGIN my_proc2(arg); END;.

  Note that the Scheduler wraps job_action in its own block and passes the following to PL/SQL for execution: DECLARE ... BEGIN job_action END; This is done to declare some internal Scheduler variables. You can include any Scheduler metadata attribute except event_message in your PL/SQL code. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value.

  See Table 140-37 for details on available metadata attributes.

• For a stored procedure:

  The action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job. If case sensitivity is needed, enclose the schema name and the store procedure name in double quotes. For example, job_action_action=>'"Schema"."Procedure"'.

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

• For an executable:

  The action is the name of the external executable, including the full path name, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at-sign ('@') and the job is local, the at-sign is replaced with the SID of the current Oracle instance.

  NOTE: Shell script syntax is not supported, only syntax for the name of and path to an executable is supported.

• For a chain:

  The action is the name of a Scheduler chain object. You must specify the schema of the chain if it resides in a different schema than the job.

• For an external script:

  The job_action must be either the path to an operating system script or an inline operating system script. If the job_action is a path to a script, then the script must reside on every computer that the job runs on. The job_action may contain calls to SQL*Plus or RMAN executables directly, without having to specify its full path, given that they are stored on their default location for every computer that runs the job.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called. The job must point to a credential that contains a valid operating system username and password.

• For a SQL script:

  The job_action must be either the path to a SQL*Plus script or an inline SQL*Plus script. If the job_action is a path to a script, then the script must reside on every computer that the job runs on.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called. If the arguments are named, they are also bound to named variables in the SQL*Plus session.

• For a backup script:

  The job_action is either the path to a RMAN script or an inline RMAN script. If the program_action is a path to a script, then the script must reside on every computer that the program runs on.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called.

number_of_arguments

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

program_name

The name of the program associated with this job. If the program is of type EXECUTABLE, the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

start_date

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

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

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

event_condition

This is a conditional expression based on the columns of the event source queue table. The expression must have the syntax of an Advanced Queuing rule. Accordingly, you can include user data properties in the expression provided that the message payload is an object type, and that you prefix object attributes in the expression with tab.user_data. For more information on rules, see the DBMS_AQADM.ADD_SUBSCRIBER procedure.

queue_spec

This argument specifies either of the following:

• The source queue where events that start this particular job are enqueued. If it is secure, then the queue_spec argument is a pair of values of the form queue_name, agent name. If it is not secure, then only the queue name need be provided. If a fully qualified queue name is not provided, the queue is assumed to be in the job owner's schema. In the case of secure queues, the agent name provided should belong to a valid agent that is currently subscribed to the queue.

• A file watcher name. For more information on this option, see Oracle Database Administrator's Guide.

repeat_interval

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

The expression specified is evaluated to determine the next time the job should run. If repeat_interval is not specified, the job runs only once at the specified start date. See "Calendaring Syntax" for further information.

schedule_name

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

job_class

The class this job is associated with.

end_date

This attribute specifies the date and time after which the job expires and is no longer run. After the end_date, if auto_drop is TRUE, the job is dropped. If auto_drop is FALSE, the job is disabled and the STATE of the job is set to COMPLETED.

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

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

comments

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

job_style

Style of the job being created. This argument can have one of the following values:

• 'REGULAR' creates a regular job. This is the default.

• 'LIGHTWEIGHT' creates a lightweight job. This value is permitted only when the job references a program object. Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

credential_name

The default credential to use with the job. Applicable only to remote database jobs, remote external jobs, local external jobs, script jobs, and event-based jobs that process file arrival events. The credential must exist.

For local database jobs, it must be NULL.

For local external jobs only, if this attribute is NULL (the default), then a preferred (default) credential is selected. See Oracle Database Administrator's Guide for information about preferred credentials for local external jobs.

See also: "CREATE_CREDENTIAL Procedure"

destination_name

The database destination or external destination for the job. Use for remote database jobs and remote external jobs only. Must be NULL for jobs running on the local database or for local external jobs (executables).

This attribute can be a single destination name or the name of a group of type 'EXTERNAL_DEST' or 'DB_DEST'. The single destination or group must already exist.

The following applies to this attribute:

• If it is a database destination, it must have been created by the CREATE_DATABASE_DESTINATION Procedure.

• If it is an external destination, it must have been implicitly created by registering a remote Scheduler agent with the local database.

• If it is a group, each member of the group must exist, and the job must run on all destinations named in the group. See "CREATE_GROUP Procedure".

destination_name cannot reference a destination group when:

• The job type is 'CHAIN'

• The job style is 'LIGHTWEIGHT'

If the credential_name argument of CREATE_JOB is NULL, each destination must be preceded by a credential, in the following format:

credential.destination

The credential must already exist. If the credential_name argument is provided, then it serves as the default credential for every destination that is not preceded by a credential.

You can query the views *_SCHEDULER_DB_DESTS and ALL_SCHEDULER_EXTERNAL DESTS for existing destinations and *_SCHEDULER_GROUP_MEMBERS for existing groups and their members.

*** destination job attribute is deprecated in Oracle Database 11gR2 and superseded by destination_name.

enabled

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

auto_drop

This flag, if TRUE, causes a job to be automatically dropped after it has completed or has been automatically disabled. A job is considered completed if:

• Its end date (or the end date of the job schedule) has passed.

• It has run max_runs number of times. max_runs must be set with SET_ATTRIBUTE.

• It is not a repeating job and has run once.

A job is disabled when it has failed max_failures times. max_failures is also set with SET_ATTRIBUTE.

If this flag is set to FALSE, the jobs are not dropped and their metadata is kept until the job is explicitly dropped with the DROP_JOB procedure.

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

job_class_name

The name to assign to the job class. Job classes can only be created in the SYS schema.

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

resource_consumer_group

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

If a resource consumer group is dropped, job classes associated with it are then associated with the default resource consumer group.

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

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

service

This attribute specifies the database service that the jobs in this class have affinity to. In an Oracle RAC environment, this means that the jobs in this class only run on those database instances that are assigned to the specific service.

Note that a service can be mapped to a resource consumer group, so you can also control resources allocated to jobs by specifying a service. See DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING for details. If both the resource_consumer_group and service attributes are specified, and if the service is mapped to a resource consumer group, the resource_consumer_group attribute takes precedence.

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

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

logging_level

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

• DBMS_SCHEDULER.LOGGING_OFF

  No logging is performed for any jobs in this class.

• DBMS_SCHEDULER.LOGGING_RUNS

  The Scheduler writes detailed information to the job log for all runs of each job in this class. This is the default.

• DBMS_SCHEDULER.LOGGING_FAILED_RUNS

  The Scheduler logs only jobs that failed in this class.

• DBMS_SCHEDULER.LOGGING_FULL

  In addition to recording every run of a job, the Scheduler records all operations performed on all jobs in this class. Every time a job is created, enabled, disabled, altered (with SET_ATTRIBUTE), stopped, and so, an entry is recorded in the log.

log_history

This attribute controls the number of days that job log entries for jobs in this class are retained. It helps prevent the job log from growing indiscriminately.

The range of valid values is 0 through1000000. If set to 0, no history is kept. If NULL (the default), retention days are set by the log_history Scheduler attribute (set with SET_SCHEDULER_ATTRIBUTE).

comments

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

jobdef_array

The array of job definitions. See "Data Structures" for a description of the JOB_DEFINITION_ARRAY and JOB_DEFINITION datatypes.

commit_semantics

The commit semantics. The following types are supported:

• STOP_ON_FIRST_ERROR returns on the first error. Previous successfully created jobs are committed to disk. This is the default.

• TRANSACTIONAL returns on the first error and everything that happened before that error is rolled back.

• ABSORB_ERRORS tries to absorb any errors and attempts to create the rest of the jobs on the list. It commits all successfully created jobs. If errors occur, you can query the view SCHEDULER_BATCH_ERRORS for details.

program_name

The name to assign to the program. The name must be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema. If no name is specified, then an error occurs.

program_type

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

• 'PLSQL_BLOCK'

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

• 'STORED_PROCEDURE'

  This specifies that the program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with INOUT or OUT arguments are not supported.

• 'EXECUTABLE'

  This specifies that the job is going to be run outside the database using an external executable. External programs imply anything that can be executed from the operating system command line. AnyData arguments are not supported with job or program type EXECUTABLE.

• 'EXTERNAL_SCRIPT'

  This specifies that the job is an external script that uses the command shell of the computer running the job. For Windows this is cmd.exe and for UNIX based systems the sh shell, unless a different interpreter is specified by prefixing the first line of the script with #!.

• 'SQL_SCRIPT'

  This specifies that the program is a SQL*Plus script.

  A job using this program must point to a credential that contains a valid operating system username and password. The SQL*Plus script is run by SQL*Plus executable. The job using this program may point to a connect credential that contains a database credential. If so, this credential is used to connect to the database before running the SQL*Plus script.

  Note that if you choose to use connect credential, you must use set_attribute to specify the Connect_Credential_Name attribute. If you do not have connect credential, you must include an explicit SQL*Plus connect statement providing a valid database userid / password.

• 'BACKUP_SCRIPT'

This specifies that the program is an RMAN backup script.

The script runs a connect statement that uses either a password or OS authentication before it executes any target commands. The Scheduler uses the RMAN executable from the current Oracle home to run the script and throws an error if this is missing.

program_action

This attribute specifies the action of the program. If program_action is not specified, an error is generated.

The following actions are possible:

• For a PL/SQL block, the action is to execute PL/SQL code. These blocks must end with a semicolon.

  For example, my_proc(); or BEGIN my_proc(); END; or DECLARE arg pls_integer:= 10; BEGIN my_proc2(arg); END;.

  Note that the Scheduler wraps job_action in its own block and passes the following to PL/SQL for execution: DECLARE ... BEGIN job_action END; This is done to declare some internal Scheduler variables. You can include any Scheduler metadata attribute except event_message in your PL/SQL code. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value. See Table 140-37 for details on available metadata attributes.

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

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

  If case sensitivity is needed, enclose the schema name and the store procedure name in double quotes. For example, program_action=>'"Schema"."Procedure"'.

• For an executable, the action is the name of the external executable, including the full path name, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at sign ('@') and the job is local, the at sign is replaced with the SID of the current Oracle instance.

• For an external script, the action must be either the path to an operating system script or an inline operating system script. If the program_action is a path to a script, then the script must reside on every computer that the program runs on. The program_action may contain calls to SQL*Plus or RMAN executables directly, without having to specify its full path, given that they are stored on their default location for every computer that runs the job.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called. The program points to a credential that contains a valid operating system username and password.

• For a SQL script, the action must be either the path to a SQL*Plus script or an inline SQL*Plus script. If the program_action is a path to a script, then the script must reside on every computer that the program runs on.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called. If the arguments are named, they are also bound to named variables in the SQL*Plus session.

• For a backup script, the action must be either the path to a RMAN script or an inline RMAN script. If the program_action is a path to a script, then the script must reside on every computer that the program runs on.

  The job can only have arguments that are strings or that can be cast to strings. These arguments are passed positionally when the script is called.

number_of_arguments

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

If the program_type is PLSQL_BLOCK, then this parameter is ignored.

enabled

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

comments

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

schedule_name

The name to assign to the schedule. The name must be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs.

start_date

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

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

If start_date is null, then the date that the job or window is enabled is used. start_date and repeat_interval cannot both be null.

repeat_interval

This attribute specifies how often the schedule repeats. It is expressed using calendaring syntax. See "Calendaring Syntax" for further information. PL/SQL expressions are not allowed as repeat intervals for named schedules.

end_date

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

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

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

comments

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

window_name

The name to assign to the window. The name must be unique in the SQL namespace. All windows are in the SYS schema, so the preface 'SYS' is optional.

resource_plan

This attribute specifies the resource plan that automatically activates when the window opens. When the window closes, the system switches to the appropriate resource plan, which is usually the plan that was in effect before the window opened, but can also be the plan of a different window.

Only one resource plan can be associated with a window. It may be NULL or the empty string (""). When it is NULL, the resource plan in effect when the window opens stays in effect for the duration of the window. When it is the empty string, the resource manager is disabled for the duration of the window.

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

start_date

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

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

duration

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

schedule_name

This attribute specifies the name of the schedule associated with the window.

repeat_interval

This attribute specifies how often the window repeats. It is expressed using the Scheduler calendaring syntax. See "Calendaring Syntax" for more information.

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

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

end_date

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

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

The end_date must be after the start_date. If it is not, then an error is generated when the window is created.

window_priority

This attribute is only relevant when two windows overlap. Because only one window can be in effect at one time, the window priority determines which window opens. The two possible values for this attribute are 'HIGH' and 'LOW'. A high priority window has precedence over a low priority window, therefore, the low priority window does not open if it overlaps a high priority window. By default, windows are created with priority 'LOW'.

comments

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

program_name

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

argument_position

The position of the argument as it is passed to the executable. Argument numbers go from one to the number_of_arguments specified for the program. This must be unique, so it can replace any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ANYDATA_VALUE Procedure.

argument_type

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

default_value

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

out_argument

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

chain_name

The name of the chain that the step is in

step_name

The name of the step

event_schedule_name

The name of the event schedule that the step waits for

timeout

This parameter is reserved for future use

event_condition

See the CREATE_EVENT_SCHEDULE Procedure

queue_spec

See the CREATE_EVENT_SCHEDULE Procedure

chain_name

The name of the chain to alter

condition

A boolean expression which must evaluate to TRUE for the action to be performed. Every chain must have a rule that evaluates to TRUE to start the chain. For this purpose, you can use a rule that has 'TRUE' as its condition if you are using Scheduler chain condition syntax, or '1=1' as its condition if you are using SQL syntax.

• Scheduler Chain Condition Syntax

  See "Scheduler Chain Condition Syntax" for details

• SQL WHERE Clause Syntax

  Conditions expressed with SQL must use the syntax of a SELECT statement WHERE clause.

  You can refer to chain step attributes by using the chain step name as a bind variable.

  The bind variable syntax is :step_name.attribute. (step_name refers to a typed object.) Possible attributes are: completed, state, start_date, end_date, error_code, and duration.

  Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'STALLED', 'SUCCEEDED', 'FAILED', and 'STOPPED'. If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE', otherwise completed is 'FALSE'.

action

The action to be performed when the rule evaluates to TRUE. The action must consist of at least one keyword with an optional value and an optional delay clause.

Possible actions include:

• [AFTER delay_interval] START step_1[,step_2 ...]

• STOP step_1[,step_2 ...]

• END [{end_value|step_name.error_code}]

At the beginning of the START action, a delay clause can specify a delay interval before performing the action. delay_interval is a formatted datetime interval of the form HH:MM:SS.

The END action ends the chain with an error code equal to either the supplied end_value or the error code that step_name completes with. The default error code is 0, indicating a successful chain run.

rule_name

The name of the rule being created. If no rule_name is given, one is generated in the form SCHED_RULE$_{N}.

comments

An optional comment describing the rule. This is stored in the rule object created.

chain_name

The name of the chain to alter.

step_name

The name of the step being defined. If a step already exists with this name, the new step replaces the old one.

program_name

The name of a program or chain to run during this step. The chain owner must have EXECUTE privileges on this program or chain.

program_name

The name of the program to be altered

metadata_attribute

The metadata to be passed. Valid metadata attributes are: 'job_name', 'job_subname', 'job_owner', 'job_start', 'window_start', 'window_end', and 'event_message'.

Table 140-37 describes these attributes in detail.

argument_position

The position of the argument as it is passed to the executable. The position cannot be greater than the number_of_arguments specified for the program. It must be unique, so it replaces any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures.

program_name

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

argument_position

The position of the argument as it is passed to the executable. Argument numbers go from one to the number_of_arguments specified for the program. This must be unique so it replaces any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ARGUMENT_VALUE Procedure.

argument_type

The datatype of the argument being defined. This is not verified or used by the Scheduler. The program user uses argument_type when deciding what value to assign to the argument. Any valid SQL datatype is allowed.

default_value

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

out_argument

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

name

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

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

If a group name is specified, then the group is disabled, but the enabled state of the group members is unaffected.

force

If TRUE, objects are disabled even if other objects depend on them. See the usage notes for more information.

commit_semantics

The commit semantics. The following types are supported:

• STOP_ON_FIRST_ERROR: The procedure returns on the first error and the previous disable operations that were successful are committed to disk.

  This is the default.

• TRANSACTIONAL: The procedure returns on the first error and everything that happened before that error is rolled back.

  This type is only supported when disabling a job or a list of jobs. In addition, this type is not supported when force is set to TRUE.

• ABSORB_ERRORS: The procedure tries to absorb any errors and disable the rest of the jobs and commits all the disable operations that were successful. If errors occur, you can query the view SCHEDULER_BATCH_ERRORS for details.

  This type is only supported when disabling a job or a list of jobs.

destination_name

A comma-separated list of external destinations to drop. Because user SYS owns all external destinations, do not prefix them with a schema name.

The procedure stops processing if it encounters an external destination that does not exist. All external destinations processed before the error are dropped.

Cannot be NULL.

chain_name

The name of the chain to drop. Can also be a comma-delimited list of chains.

force

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, all jobs pointing to the chain are disabled before the chain is dropped.Running jobs that point to this chain are stopped before the chain is dropped.

chain_name

The name of the chain to alter

rule_name

The name of the rule to drop

force

If force is set to TRUE, the drop operation proceeds even if the chain is currently running. The running chain is not stopped or interrupted. If force is set to FALSE and the chain is running, an error is generated.

chain_name

The name of the chain to alter

step_name

The name of the step being dropped. Can be a comma-separated list.

force

If force is set to TRUE, this succeeds even if this chain is currently running. The running chain will not be stopped or interrupted.If force is set to FALSE and this chain is currently running, an error is thrown.

credential_name

The name of the credential being dropped. This can optionally be prefixed with a schema name. This cannot be set to NULL.

force

If set to FALSE, the credential must not be referenced by any job, or an error will occur. If set to TRUE, the credential is dropped whether or not there are jobs referencing it. Jobs that reference the credential will continue to point to a nonexistent credential and throw an error at runtime.

destination_name

The name of the destination to drop. Can be a comma-separated list of database destinations to drop. Each database destination can optionally be prefixed with a schema name.

The procedure stops processing if it encounters a database destination that does not exist. All database destinations processed before the error are dropped.

Cannot be NULL.

file_watcher_name

The file watcher to drop. Can be a comma-separated list of file watchers. Each file watcher name can optionally be prefixed with a schema name.

Cannot be NULL.

force

If set to FALSE, the file watcher must not be referenced by any job, or an error occurs. If set to TRUE, the file watcher is dropped whether or not there are jobs referencing it. In this case, jobs that reference the dropped file watcher are disabled.

group_name

A group to drop. Can be a comma-separated list of group names. Each group name can optionally be prefixed with a schema name.

The procedure stops processing if it encounters a group that does not exist. All groups processed before the error are dropped.

Cannot be NULL.

force

If FALSE, the group must not be referenced by any job, otherwise an error occurs. If TRUE, the group is dropped whether or not there are jobs referencing it. In this case, all jobs referencing the group are disabled and all job instances that reference the group are removed from the *_SCHEDULER_JOB_DESTS views.

job_name

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

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

force

If force is set to TRUE, the Scheduler first attempts to stop the running job instances (by issuing the STOP_JOB call with the force flag set to false), and then drops the jobs.

defer

If defer is set to TRUE, the Scheduler allows the running jobs to complete and then drops the jobs.

commit_semantics

The commit semantics. The following types are supported:

• STOP_ON_FIRST_ERROR returns on the first error and previous successful drop operations are committed to disk. This is the default.

• TRANSACTIONAL returns on the first error. Everything that happened before that error is rolled back. This type is not supported when force is set to TRUE.

• ABSORB_ERRORS tries to absorb any errors and drop the rest of the jobs, and commits all the successful drops. If errors occur, you can query the view SCHEDULER_BATCH_ERRORS for details.

Only STOP_ON_FIRST_ERROR is permitted when job classes are included in the job_name list.

job_class_name

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

force

If force is set to FALSE, a class being dropped must not be referenced by any jobs, otherwise an error occurs.

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

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

program_name

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

force

If force is set to FALSE, the program must not be referenced by any job, otherwise an error occurs.

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

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

program_name

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

argument_name

The name of the argument being dropped

argument_position

The position of the argument to be dropped

schedule_name

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

force

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

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

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

window_name

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

force

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

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

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

name

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

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

If a group name is specified, then the group is enabled, but the enabled state of the group members is unaffected.

commit_semantics

The commit semantics. The following types are supported:

• STOP_ON_FIRST_ERROR - The procedure returns on the first error and previous successful enable operations are committed to disk. This is the default.

• TRANSACTIONAL - The procedure returns on the first error and everything that happened before that error is rolled back.

  This type is only supported when enabling a job or a list of jobs.

• ABSORB_ERRORS - The procedure tries to absorb any errors and enable the rest of the jobs. It commits all the enable operations that were successful. If errors occur, you can query the view SCHEDULER_BATCH_ERRORS for details.

  This type is only supported when enabling a job or a list of jobs.

job_name

The name of the job to end. Must be a detached job that is running.

error_number

If zero, then the job run is logged as succeeded. If -1013, then the job run is logged as stopped. If non-zero, then the job run is logged as failed with this error number.

additional_info

This text is stored in the additional_info column of the *_scheduler_job_run_details views for this job run.

calendar_string

The calendar expression to be evaluated. The string must be in the calendaring syntax described in "Operational Notes".

start_date

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

return_date_after

The return_date_after argument helps the Scheduler determine which one of all possible matches (all valid execution dates) to return from those determined by the start_date and the calendar string.

When a NULL value is passed for this argument, the Scheduler automatically fills in systimestamp as its value.

next_run_date

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

job_name

The name of the running job (pointing to a chain) to reevaluate the rules for

prefix

The prefix to use when generating the job name

agent_name

The name of an external destination where the agent is running

attribute

Possible Attributes values

• VERSION:. Returns the agent version number. Requires the CREATE JOB system privilege.

• UPTIME: Returns the time the agent has been up and running. Requires the CREATE JOB system privilege.

• NUMBER_OF_RUNNING_JOBS: Returns the number of jobs that the agent is currently running. Requires the CREATE JOB system privilege.

• TOTAL_JOBS_RUN: Returns the number of jobs run by the agent since it was started. Requires the CREATE JOB system privilege.

• RUNNING_JOBS: Returns a comma-separated list of the names of the jobs running currently. Requires the MANAGE SCHEDULER system privilege.

• ALL: Returns all the information the previous options return. It requires the MANAGE SCHEDULER system privilege.

agent_host

Either the hostname and port on which the agent is running in the form hostname:port or the name of the agent as shown in the destination_name column of the ALL_SCHEDULER_EXTERNAL_DESTS view which lists all Scheduler agents registered with the database.

name

The name of the object

attribute

The attribute being retrieved. See the SET_ATTRIBUTE Procedure for tables of attribute values.

value

The existing value of the attribute

value2

The value2 argument is for an optional second value.

Most attributes have only one value associated with them, but some can have two.

source_file

Fully qualified path name of the file to retrieve from the operating system. The file name is case-sensitive and is not converted to uppercase. If the file name starts with a question mark ('?'), the question mark is replaced by the path to the Oracle home if getting a file from the local host, or to the Scheduler agent home if getting a file from a remote host.

If the format of this parameter is external_log_id_stdout, then the stdout from the designated external job run is returned.

If the format of this parameter is external_log_id_stderr, the error text from the designated external job run is returned.

You obtain the value of external_log_id from the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views. This column contains a set of name/value pairs in an indeterminate order, so you must parse this column for the external_log_id name/value pair, and then append either "_stdout" or "_stderr" to its value.

The external job must have an associated credential. The credential_name parameter of GET_FILE must name the same credential that is used by the job, and the source_host parameter must be the same as the destination attribute of the job.

source_host

If the file is to be retrieved from a remote host, then this parameter must be a valid an external destination name. (An external destination is created when you register a remote Scheduler agent with the database. You can view external destination names in the views *_SCHEDULER_EXTERNAL_DESTS.)

If source_host is NULL or set to 'localhost', then the file is retrieved from the file system of the local host. To determine the port number of a Scheduler agent, view the schagent.conf file, which is located in the Scheduler agent home directory on the remote host.

credential_name

The name of the credential to use for accessing the file system.

file_contents

The variable into which the file contents is read.

destination_file_name

The file to which the file contents is written.

destination_directory_object

The directory object that specifies the path to the destination file, when destination_file_name is used. The caller must have the necessary privileges on the directory object.

destination_permissions

Reserved for future use

attribute

The name of the attribute

value

The existing value of the attribute

current_open_window

Name of the currently open window

default_timezone

Default time zone used by the Scheduler for repeat intervals and windows

email_sender

The default e-mail address of the sender for job state e-mail notifications

email_server

The SMTP server address that the Scheduler uses to send e-mail notifications for job state events. E-mail notifications cannot be sent if this attribute is NULL.

event_expiry_time

Time in seconds before an event generated by the Scheduler and enqueued onto the Scheduler event queue expires. May be NULL.

log_history

Retention period in days for job and window logs. The range of valid values is 0 through 1000000.

max_job_slave_processes

This Scheduler attribute is not used.

window_name

The name of the window

duration

The duration of the window. It is of type interval day to second. If it is NULL, then the window opens for the regular duration as specified in the window metadata.

force

If force is set to FALSE, then opening an already open window generates an error.

If force is set to TRUE:

You can open a window that is already open. The window stays open for the duration specified in the call, from the time the OPEN_WINDOW command was issued.

For example: window1 was created with a duration of four hours. It has how been open for two hours. If, at this point, you reopen window1 using the OPEN_WINDOW call and do not specify a duration, then window1 stays open for four hours because it was created with that duration. If you specified a duration of 30 minutes, the window will close in 30 minutes.

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

log_history

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

which_log

This specifies the log type. Valid values are: job_log, window_log, and job_and_window_log.

job_name

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

destination_file

Fully qualified path name of the file to save to the operating system file system. The file name is case-sensitive. If the file name starts with a question mark ('?'), the question mark is replaced by the path to the Oracle home if saving to the local host, or to the Scheduler agent home if saving to a remote host.

destination_host

If NULL or set to 'localhost', the file is saved to the file system of the local computer.

To save to a remote host, this parameter must be a valid external destination name. (An external destination is created when you register a remote Scheduler agent with the database. You can view external destination names in the views *_SCHEDULER_EXTERNAL_DESTS.)

credential_name

The name of the credential to use for accessing the destination file system.

file_contents

The variable from which the file contents is read.

source_file_name

The file from which the file contents is written

source_directory_object

The directory object that specifies the path to the source file, when source_file_name is used. The caller must have the necessary privileges on the directory object.

destination_permissions

Reserved for future use

subscriber_name

Name of the Oracle Streams Advanced Queuing (AQ) agent to remove the subscription from. If NULL, the user name of the calling user is used.

group_name

The name of the group.

member_name

The name of the member to remove from group. Comma-separated list of members to remove. An error is returned if any of the members is not part of the group.

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

If the member is a destination, any job instances that run on this destination are removed from the *_SCHEDULER_JOB_DESTS views.

job_name

Name of the job to remove e-mail notifications for. Cannot be NULL.

recipients

E-mail address to remove e-mail notification for. Comma-separated list of e-mail addresses.

events

Job state event to remove e-mail notification for. Comma-separate list of job state events.

job_name

The name of the job being altered

argument_position

The position of the program argument being reset

argument_name

The name of the program argument being reset

chain_name

The name of the chain to run

job_name

The name of the job to create to run the chain

start_steps

Comma-separated list of the steps to start when the chain starts running

step_state_list

List of chain steps with an initial state (SUCCEEDED or FAILED) to set for each.

Set the attributes of sys.scheduler$_step_type as follows:

step_name The name of the stepstep_type 'SUCCEEDED' or 'FAILED error_number'

where error_number is a positive or negative integer.

job_name

A job name or a comma-separate list of entries, where each is the name of an existing job, optionally preceded by a schema name and dot separator.

If you specify a multiple-destination job, the job runs on all destinations. In this case, the use_current_session argument must be FALSE.

use_current_session

This specifies whether or not the job run should occur in the same session that the procedure was invoked from.The job always runs as the job owner, in the job owner's schema, unless it has credential specified, then the job runs using the user named in the credential.

When use_current_session is set to TRUE:

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

• state, run_count, last_start_date, last_run_duration, and failure_count of *_scheduler_jobs are not updated.

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

When use_current_session is set to FALSE:

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

• All relevant fields in *_scheduler_jobs are updated.

• RUN_JOB fails if a regularly scheduled job is running.

For jobs that have a specified destination or destination group, or point to chains or programs with the detached attribute set to TRUE, use_current_session must be FALSE.

registration_password

This is the password that remote agents must specify in order to successfully register with the database. If this is NULL, then no agents will be able to register with the database.

expiration_date

If this is set to a non-NULL value, then the registration_password is not valid after this date. After this date, no agents can register with the database. This cannot be set to a date in the past.

max_uses

This is the maximum number of successful registrations that can be performed with this password. After the number of successful registrations has been performed with this password, then no agents can register with the database. This cannot be set to 0 or a negative value. If this is set to NULL, then there will be no limit on the number of successful registrations.

name

The name of the object.

attribute

See Table 140-77 through Table 140-87.

value

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

value2

The value2 argument is for an optional second value. Most attributes have only one value associated with them, but some can have two.

Job

Table 140-77

Program

Table 140-79

Schedule

Table 140-80

File Watcher

Table 140-81

Job Class

Table 140-82

Window

Table 140-83

Chain

Table 140-84

Database Destination

Table 140-85

External Destination

Table 140-86

Group

Table 140-87

Credential

Table 140-88

allow_runs_in_restricted_mode

If TRUE, the job is permitted to run when the database is in restricted mode, provided that the job owner is permitted to log in during this mode. FALSE by default.

auto_drop

This attribute, if TRUE, causes a job to be automatically dropped after it completes or is automatically disabled. A job is considered completed if:

• Its end date (or the end date of the schedule) has passed.

• It has run max_runs number of times. max_runs must be set with SET_ATTRIBUTE.

• It is not a repeating job and has run once.

A job is automatically disabled when it has failed max_failures times. max_failures is also set with SET_ATTRIBUTE.

If this attribute is set to FALSE, the jobs are not dropped and their metadata is kept until the job is explicitly dropped with the DROP_JOB procedure.

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

comments

An optional comment.

connect_credential_name

This attribute may be set to point to a database credential. For a SQL*Plus or backup script job, the credential connects to the database before running the script. For other job types, it is ignored. The job owner must have execute privileges on the credential, otherwise the job fails.

Using a connect_credential_name is recommended since it allows the password to be stored securely in a credential in the database rather than in plain view in the job, program action, or script.

credential_name

This attribute specifies the name of the credential object (credential) to use for a remote database job, a remote external job, a local external job, or an event-based job that processes a file arrival event. For local external jobs only, if this attribute is NULL (the default), then a preferred (default) credential is selected. See Oracle Database Administrator's Guide for information about preferred credentials for local external jobs.

See also: "CREATE_CREDENTIAL Procedure"

database_role

This attribute applies when the database participates in an Oracle Data Guard environment. If this attribute is set to 'PRIMARY', the job runs only when the database is in the role of the primary database. If set to 'LOGICAL STANDBY', the job runs only when the database is in the role of a logical standby. The default is 'PRIMARY' when the database is the primary database, and 'LOGICAL STANDBY' when the database is a logical standby.

Note: If you want a job to run for all database roles on a particular host, you must create two copies of the job on that host: one with a database_role of 'PRIMARY', and the other with a database_role of 'LOGICAL STANDBY'.

destination

*** Deprecated in Oracle Database 11g Release 2. Use destination_name instead.

This attribute specifies a host on which to run a remote external job. It must be set to the host name or IP address of the destination host. It can optionally be followed by a port number, in the following format:

hostname:port

This attribute is set to NULL by default.

destination_name

The database destination or external destination for the job. Use for remote database jobs and remote external jobs only. For jobs running on the local database or for local external jobs (executables), must be NULL.

See Table 140-26 for details about this attribute.

end_date

Specifies the date and time after which the job expires and is no longer run. After the end_date, if is TRUE, the job is dropped. If auto_drop is FALSE, the job is disabled and the STATE of the job is set to COMPLETED.

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

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

event_spec

This attribute takes two values: the value argument specifies the event condition and the value2 argument specifies the queue specification. For more details, see the descriptions for the event_condition and queue_spec arguments in the "CREATE_JOB Procedure".

follow_default_timezone

If TRUE and if the job start_date is null, then when the default_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

For example, if the job was set to run at 02:00 in the previous time zone, it will run at 02:00 in the new time zone.

If the job start_date is not null, then the time zone for the run date and time for the job is always specified by the time zone of the start_date.

If FALSE, the next start date and time for the job is not recomputed when the default_timezone scheduler attribute is changed. In this case, if the old time zone is three hours earlier than the new time zone, then a job scheduled to run at 02:00 in the old time zone runs at 05:00 in the new time zone.

Summer and winter transitions do not change the default time zone name.

instance_id

Valid only in an Oracle Real Application Clusters environment. Indicates the instance on which the job is to be run.

instance_stickiness

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

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

For environments other than Oracle RAC, this attribute is not useful because there is only one instance.

job_action

The action that the job performs, depending on the job_type attribute. For example, if job_type is 'STORED_PROCEDURE', job_action contains the name of the stored procedure.

job_class

The class this job is associated with.

job_priority

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

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

job_type

The type of this job.Valid values are: 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', CHAIN', 'EXTERNAL_SCRIPT', 'SQL_SCRIPT', and 'BACKUP_SCRIPT'.

If this is set, program_name must be NULL.

job_weight

*** Deprecated in Oracle Database 11gR2. Do not change the value of this attribute from the default, which is 1.

Weight of the job for parallel execution.

logging_level

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

DBMS_SCHEDULER.LOGGING_OFF

(The default) No logging is performed for this job. However, the logging level of the job class takes precedence and job logging may occur.

DBMS_SCHEDULER.LOGGING_FAILED_RUNS

The Scheduler logs only jobs that failed, with the reason for failure. If the job class has a higher logging level, then the higher logging level takes precedence.

DBMS_SCHEDULER.LOGGING_RUNS

The Scheduler writes detailed information to the job log for all runs of each job in this class. If the job class has a higher logging level, then the higher logging level takes precedence.

DBMS_SCHEDULER.LOGGING_FULL

In addition to recording every run of a job, the Scheduler records all operations performed on the job, including create, enable, disable, alter (with SET_ATTRIBUTE), stop, and so on.

max_failures

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

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

max_run_duration

This attribute specifies the maximum amount of time that the job should be allowed to run. Its datatype is INTERVAL DAY TO SECOND. If this attribute is set to a non-zero and non-NULL value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.

max_runs

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

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

number_of_arguments

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

parallel_instances

This is a boolean attribute that can be set only for event-based jobs.

If FALSE (the default), then if an event is raised and the event-based job that processes that event is already running, the new event is ignored.

If TRUE, then an instance of the job is started for every instance of the event, and each job instance is a lightweight job so multiple instances of the same event-based job can run in parallel. Each lightweight job takes its attributes (such as action, maximum run duration, and so on) from the definition of the event-based job (its parent job). After the lightweight job completes, it is dropped. There is no explicit limit to the number of lightweight jobs that can run simultaneously to process multiple instances of the event. However, limitations may be imposed by available system resources.

The lightweight jobs are not visible in any of the *_SCHEDULER_JOBS views. However, they are visible in the *_SCHEDULER_RUNNING_JOBS views. The name of each lightweight job is the same as that of the parent job, and a subname is automatically generated to distinguish each lightweight job from its parent and from its siblings.

program_name

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

raise_events

This attribute tells the Scheduler at what stages of the job execution to raise events. It is a bit vector in which zero or more of the following bits can be set. Each bit has a package constant corresponding to it.

• job_started CONSTANT PLS_INTEGER := 1

• job_succeeded CONSTANT PLS_INTEGER := 2

• job_failed CONSTANT PLS_INTEGER :=4

• job_broken CONSTANT PLS_INTEGER :=8

• job_completed CONSTANT PLS_INTEGER :=16

• job_stopped CONSTANT PLS_INTEGER :=32

• job_sch_lim_reached CONSTANT PLS_INTEGER :=64

• job_disabled CONSTANT PLS_INTEGER :=128

• job_chain_stalled CONSTANT PLS_INTEGER :=256

• job_all_events CONSTANT PLS_INTEGER := 511

• job_run_completed CONSTANT PLS_INTEGER := job_succeeded + job_failed + job_stopped

Table 140-78 describes these event types in detail.

repeat_interval

Either a PL/SQL function returning the next date and time on which to run, or calendaring syntax expression. If this is set, schedule_name should be NULL. See "Calendaring Syntax" for more information.

restartable

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

In the case of a chain job, if this attribute is TRUE, the chain is restarted from the beginning after an application failure. If this attribute is FALSE, or if there has been a database failure, the chain is restarted at the last running step. The restart_on_recovery attribute of that step then determines if the step is restarted or marked as stopped. (If marked as stopped, the chain evaluates rules and continues.)

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

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

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

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

The Scheduler stops retrying a job when:

• One of the retries succeeds.

• All of its six retries have failed.

• The next retry would occur after the next regularly scheduled run of the job.

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

schedule_limit

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

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

schedule_name

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

start_date

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

stop_on_window_close

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

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

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

store_output

This is a boolean attribute. If set to TRUE, then for job runs that are logged, all job output and error messages are stored in the *_JOB_RUN_DETAILS views. If set to FALSE, then the output and messages are not stored. For new jobs, this is set, by default, to TRUE.

job_all_events

Not an event, but a constant that provides an easy way for you to enable all events

job_broken

The job has been disabled and has changed to the BROKEN state because it exceeded the number of failures defined by the max_failures job attribute

job_chain_stalled

A job running a chain is in the CHAIN_STALLED state. A running chain becomes stalled if there are no steps running or scheduled to run and the chain evaluation_interval is set to NULL. No progress is made in the chain unless there is manual intervention.

job_completed

The job completed because it reached its max_runs or end_date

job_disabled

The job was disabled by the Scheduler or by a call to SET_ATTRIBUTE

job_failed

The job failed, either due to an error or an abnormal termination.

job_over_max_dur

The job exceeded the maximum run duration specified by its max_run_duration attribute. (Note: you do not need to enable this event with the raise_events job attribute; it is always enabled.)

job_run_completed

A job run either failed, succeeded, or was stopped

job_sch_lim_reached

The schedule limit of the job was reached. The job was not started because the delay in starting the job exceeded the value of the schedule_limit job attribute.

job_started

The job started

job_stopped

The job was stopped by a call to STOP_JOB

job_succeeded

The job completed successfully

comments

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

detached

If TRUE, the program is a detached program. See Oracle Database Administrator's Guide for information about detached jobs and detached programs.

number_of_arguments

The number of arguments required by the stored procedure or other executable that the program invokes

program_action

The action that the program performs, indicated by the program_type attribute. For example, if program_type is 'STORED_PROCEDURE', program_action contains the name of the stored procedure.

program_type

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

comments

An optional comment.

end_date

The cutoff date and time after which the schedule does not specify any dates.

event_spec

This attribute takes two values: the value argument should contain the event condition and the value2 argument should contain the queue specification. For more details, see the descriptions for the event_condition and queue_spec arguments to the "CREATE_JOB Procedure".

repeat_interval

An attribute specifying how often the schedule should repeat, using the calendaring syntax. See "Calendaring Syntax" for more information.

start_date

The start or reference date and time used by the calendaring syntax.

destination

Remote host name or IP address where the file is expected to arrive. If NULL, destination is the local host.

directory_path

Directory in which the file is expected to arrive. The single wildcard '?' at the beginning of the path denotes the Oracle home path. For example, '?/rdbms/log' denotes the rdbms/log subdirectory of the Oracle home directory.

file_name

Name of the file being looked for. Two wildcards are permitted anywhere in the file name: '?' denotes any single character, and '*' denotes zero or more characters. This attribute cannot be NULL.

credential_name

Name of a valid credential object. The file watcher uses the credential to authenticate itself with the host operating system to access the watched-for file. The file watcher owner must have the EXECUTE privilege on the credential. Cannot be NULL.

min_file_size

Minimum file size in bytes before the file watcher considers the file found. Default is 0.

steady_state_duration