Table 29-1 illustrates common job tasks and their appropriate procedures and privileges:

See "Scheduler Privileges" for further information regarding privileges.

See the CREATE_JOB procedure in Oracle Database PL/SQL Packages and Types Reference for details on job attributes.

All jobs can be altered, and, except for the job name, all job attributes can be changed. If there is a running instance of the job when the change is made, it is not affected by the call. The change is only seen in future runs of the job.

In general, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views. The attributes of a job are available in the *_SCHEDULER_JOBS views.

It is valid for running jobs to alter their own job attributes. However, these changes do not take effect until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE, SET_ATTRIBUTE_NULL, and SET_JOB_ATTRIBUTES procedures.

The following example changes the repeat_interval of the job update_sales to once per week on Wednesday.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name         =>  'update_sales',
   attribute    =>  'repeat_interval',
   value        =>  'freq=weekly; byday=wed');
END;
/

There are three ways in which a job can be run:

• According to the job schedule—In this case, provided that the job is enabled, the job is automatically picked up by the Scheduler job coordinator and run under the control of a job slave. The job runs as the user who is the job owner, or in the case of a local external job with a credential, as the user named in the credential. To find out whether the job succeeded, you must query the job views (*_SCHEDULER_JOBS) or the job log (*_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS). See "How Jobs Execute" for more information job slaves and the Scheduler architecture.

• When an event occurs—Enabled event-based jobs start when a specified event is received on an event queue or when a file watcher raises a file arrival event. (See "Using Events to Start Jobs ".) Event-based jobs also run under the control of a job slave and run as the user who owns the job, or in the case of a local external job with a credential, as the user named in the credential. To find out whether the job succeeded, you must query the job views or the job log.

• By calling DBMS_SCHEDULER.RUN_JOB—You can use the RUN_JOB procedure to test a job or to run it outside of its specified schedule. You can run the job asynchronously, which is similar to the previous two methods of running a job, or synchronously, in which the job runs in the session that called RUN_JOB, and as the user logged in to that session. The use_current_session argument of RUN_JOB determines whether a job runs synchronously or asynchronously.

  RUN_JOB accepts a comma-delimited list of job names.

  The following example asynchronously runs two jobs:

  BEGIN
    DBMS_SCHEDULER.RUN_JOB(
      JOB_NAME            => 'DSS.ETLJOB1, DSS.ETLJOB2',
      USE_CURRENT_SESSION => FALSE);
  END;
  /

  Note:

  It is not necessary to call RUN_JOB to run a job according to its schedule. Provided that job is enabled, the Scheduler runs it automatically.

STOP_JOB accepts a comma-delimited list of jobs, job classes, and job destination IDs. A job destination ID is a number, assigned by the Scheduler, that represents a unique combination of a job, a credential, and a destination. It serves as a convenient method for identifying a particular child job of a multiple-destination job and for stopping just that child. You obtain the job destination ID for a child job from the *_SCHEDULER_JOB_DESTS views.

If a job class is supplied, all running jobs in the job class are stopped. For example, the following statement stops job job1, all jobs in the job class dw_jobs, and two child jobs of a multiple-destination job:

BEGIN
  DBMS_SCHEDULER.STOP_JOB('job1, sys.dw_jobs, 984, 1223');
END;
/

All instances of the designated jobs are stopped. After stopping a job, the state of a one-time job is set to STOPPED, and the state of a repeating job is set to SCHEDULED (because the next run of the job is scheduled). In addition, an entry is made in the job log with OPERATION set to 'STOPPED', and ADDITIONAL_INFO set to 'REASON="Stop job called by user: username"'.

By default, the Scheduler tries to gracefully stop a job using an interrupt mechanism. This method gives control back to the slave process, which can collect statistics of the job run. If the force option is set to TRUE, the job is abruptly terminated and certain run-time statistics might not be available for the job run.

Stopping a job that is running a chain automatically stops all running steps (by calling STOP_JOB with the force option set to TRUE on each step).

You can use the commit_semantics argument of STOP_JOB to control the outcome if multiple jobs are specified and errors occur when trying to stop one or more jobs. If you set this argument to ABSORB_ERRORS, the procedure may be able to continue after encountering an error and attempt to stop the remaining jobs. If the procedure indicates that errors occurred, you can query the view SCHEDULER_BATCH_ERRORS to determine the nature of the errors. See "Dropping Jobs" for a more detailed discussion of commit semantics.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the STOP_JOB procedure.

The mechanism described in this section applies only to remote external jobs on the UNIX and Linux platforms.

On UNIX and Linux, a SIGTERM signal is sent to the process launched by the Scheduler. The implementor of the external job is expected to trap the SIGTERM in an interrupt handler, clean up whatever work the job has done, and exit.

On Windows, STOP_JOB with force set to FALSE is supported. The process launched by the Scheduler is a console process. To stop it, the Scheduler sends a CTRL+BREAK to the process. The CTRL+BREAK can be handled by registering a handler with the SetConsoleCtrlHandler() routine.

DROP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all jobs in the job class are dropped, although the job class itself is not dropped. You cannot use job destination IDs with DROP_JOB to drop the child of a multiple-destination job.

Use the DROP_JOB_CLASS procedure to drop a job class, as described in "Dropping Job Classes".

The following statement drops jobs job1 and job3, and all jobs in job classes jobclass1 and jobclass2:

BEGIN
  DBMS_SCHEDULER.DROP_JOB ('job1, job3, sys.jobclass1, sys.jobclass2');
END;
/

When you set the force option to TRUE, the Scheduler first attempts to stop the running job by using an interrupt mechanism, calling STOP_JOB with the force option set to FALSE. If the job stops successfully, it is then dropped. Alternatively, you can first call STOP_JOB to stop the job and then call DROP_JOB. If STOP_JOB fails, you can call STOP_JOB with the force option, provided you have the MANAGE SCHEDULER privilege. You can then drop the job. By default, force is set to FALSE for both the STOP_JOB and DROP_JOB procedures.

When you set the defer option to TRUE, the running job is allowed to complete and then dropped. The force and defer options are mutually exclusive; setting both results in an error.

Possible values for this argument are:

• STOP_ON_FIRST_ERROR, the default—The call returns on the first error and commits previous successful drop operations to disk.

• TRANSACTIONAL—The call returns on the first error and rolls back previous drop operations before the error. force must be FALSE.

• ABSORB_ERRORS—The call tries to absorb any errors, attempts to drop the rest of the jobs, and commits all the drops that were successful.

Setting commit_semantics is valid only when no job classes are included in the job_name list. When you include job classes, default commit semantics (STOP_ON_FIRST_ERROR) are in effect.

The following example drops the jobs myjob1 and myjob2 with the defer option and uses transactional commit semantics:

BEGIN
  DBMS_SCHEDULER.DROP_JOB(
     job_name         => 'myjob1, myjob2',
     defer            => TRUE,
     commit_semantics => 'TRANSACTIONAL');
END;
/

This next example illustrates the ABSORB_ERRORS commit semantics. Assume that myjob1 is running when the procedure is called and that myjob2 is not.

BEGIN
  DBMS_SCHEDULER.DROP_JOB(
     job_name         => 'myjob1, myjob2',
     commit_semantics => 'ABSORB_ERRORS');
END;
/
Error report:
ORA-27362: batch API call completed with errors

You can query the view SCHEDULER_BATCH_ERRORS to determine the nature of the errors.

SELECT object_name, error_code, error_message FROM scheduler_batch_errors;

OBJECT_NAME    ERROR CODE ERROR_MESSAGE
-------------- ---------- ---------------------------------------------------
STEVE.MYJOB1        27478 "ORA-27478: job "STEVE.MYJOB1" is running

Checking USER_SCHEDULER_JOBS, you would find that myjob2 was successfully dropped and that myjob1 is still present.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_JOB procedure.

Jobs can also become disabled by other means. For example, dropping a job class disables the class jobs. Dropping either the program or the schedule that jobs point to, disables the jobs. However, disabling either the program or the schedule that jobs point to does not disable the jobs, and therefore, results in errors when the Scheduler tries to run them.

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

When a currently running job is disabled with the force option set to FALSE, an error returns. When force is set to TRUE, the job is disabled, but the currently running instance is allowed to finish.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous successful disable operations are committed to disk. If commit_semantics is set to TRANSACTIONAL and force is set to FALSE, then the call returns on the first error and rolls back the previous disable operations before the error. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to disable the rest of the jobs and commits all the successful disable operations. If the procedure indicates that errors occurred, you can query the view SCHEDULER_BATCH_ERRORS to determine the nature of the errors.

By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can also disable several jobs in one call by providing a comma-delimited list of job names or job class names to the DISABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
  DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.

The effect of this procedure is that the job will be picked up by the job coordinator for processing. Jobs are created disabled by default, so you must enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

If you enable a disabled job, it begins to run immediately according to its schedule. Enabling a disabled job also resets the job RUN_COUNT, FAILURE_COUNT, and RETRY_COUNT attributes.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous successful enable operations are committed to disk. If commit_semantics is set to TRANSACTIONAL, then the call returns on the first error and the previous enable operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to enable the rest of the jobs and commits all the successful enable operations. If the procedure indicates that errors occurred, you can query the view SCHEDULER_BATCH_ERRORS to determine the nature of the errors.

By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
 DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.

This call copies all the attributes of the old job to the new job (except job name). The new job is created disabled.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the COPY_JOB procedure.

Table 29-3 illustrates common program tasks and their appropriate procedures and privileges:

See "Scheduler Privileges" for further information regarding privileges.

See the DBMS_SCHEDULER.CREATE_PROGRAM procedure in Oracle Database PL/SQL Packages and Types Reference for details on program attributes.

If any currently running jobs use the program that you altered, they continue to run with the program as defined before the alter operation.

The following example changes the executable that program my_program1 runs:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           => 'my_program1',
   attribute      => 'program_action',
   value          => '/usr/local/bin/salesreports1');
END;
/

When the program is dropped, any arguments that pertain it are also dropped. You can drop several programs in one call by providing a comma-delimited list of program names. For example, the following statement drops three programs:

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM('program1, program2, program3');
END;
/

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

If you set the force argument to TRUE, jobs pointing to this program are disabled and the program is dropped. If you set the force argument to FALSE, the default, the call fails if there are any jobs pointing to the program.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_PROGRAM procedure.

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

The DISABLE call does not affect running jobs that point to the program and they are allowed to continue. Also, disabling the program does not affect any arguments that pertain to it.

A program can also be disabled by other means, for example, if a program argument is dropped or the number_of_arguments is changed so that no arguments are defined.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.

When a program is enabled, the enabled flag is set to TRUE. Programs are created disabled by default, therefore, you have to enable them before you can enable jobs that point to them. Before programs are enabled, validity checks are performed to ensure that the action is valid and that all arguments are defined.

You can enable several programs in one call by providing a comma-delimited list of program names to the ENABLE procedure call. For example, the following statement enables three programs:

BEGIN
  DBMS_SCHEDULER.ENABLE('program1, program2, program3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.

Table 29-4 illustrates common schedule tasks and the procedures you use to handle them.

See "Scheduler Privileges" for further information.

Schedules are created in the schema of the user creating the schedule, and are enabled when first created. You can create a schedule in another user's schema. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explicitly grant access to the schedule. The following example create a schedule:

BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'my_stats_schedule',
  start_date        => SYSTIMESTAMP,
  end_date          => SYSTIMESTAMP + INTERVAL '30' day,
  repeat_interval   => 'FREQ=HOURLY; INTERVAL=4',
  comments          => 'Every 4 hours');
END;
/

Altering a schedule changes the definition of the schedule. With the exception of schedule name, all attributes can be changed. The attributes of a schedule are available in the *_SCHEDULER_SCHEDULES views.

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

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure.

This procedure call deletes the schedule object from the database.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_SCHEDULE procedure.

Table 29-6 illustrates common tasks involving chains and the procedures associated with them.

You must ensure that you have the required privileges first. See "Setting Chain Privileges" for details.

After creating the chain object with CREATE_CHAIN, you define chain steps and chain rules separately.

The following example creates a chain:

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => NULL,
   comments            => 'My first chain');
END;
/

The rule_set_name and evaluation_interval arguments are typically left NULL. evaluation_interval can define a repeating interval at which chain rules get evaluated. rule_set_name refers to a rule set as defined within Oracle Streams.

Each step can point to one of the following:

• A Scheduler program object (program)

• Another chain (a nested chain)

• An event schedule, inline event, or file watcher

You define a step that points to a program or nested chain by using the DEFINE_CHAIN_STEP procedure. The following example adds two steps to my_chain1:

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step1',
   program_name    =>  'my_program1');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step2',
   program_name    =>  'my_chain2');
END;
/

The named program or chain does not have to exist when you define the step. However, it must exist and be enabled when the chain runs, otherwise an error is generated.

You define a step that waits for an event to occur by using the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule, can include an inline queue specification and event condition, or can include a file watcher name. This example creates a third chain step that waits for the event specified in the named event schedule:

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name           =>  'my_chain1',
   step_name            =>  'my_step3',
   event_schedule_name  =>  'my_event_schedule');
END;
/

An event step does not wait for its event until the step is started.

Steps That Run Local External Executables

After defining a step that runs a local external executable, you must use the ALTER_CHAIN procedure to assign a credential to the step, as shown in the following example:

BEGIN
  DBMS_SCHEDULER.ALTER_CHAIN('chain1','step1','credential_name','MY_CREDENTIAL');
END;
/

Steps That Run on Remote Destinations

After defining a step that is to run an external executable on a remote host or a database program unit on a remote database, you must use the ALTER_CHAIN procedure to assign both a credential and a destination to the step, as shown in the following example:

BEGIN
 DBMS_SCHEDULER.ALTER_CHAIN('chain1','step2','credential_name','DW_CREDENTIAL');
 DBMS_SCHEDULER.ALTER_CHAIN('chain1','step2','destination_name','DBHOST1_ORCLDW');
END;
/

Making Steps Restartable

After a database recovery, by default, steps that were running are marked as STOPPED and the chain continues. You can specify the chain steps to restart automatically after a database recovery by using ALTER_CHAIN to set the restart_on_recovery attribute to TRUE for those steps.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DEFINE_CHAIN_STEP, DEFINE_CHAIN_EVENT_STEP, and ALTER_CHAIN procedures.

Chain rules define when steps run and define dependencies between steps. Each rule has a condition and an action. Whenever rules are evaluated, if a condition of a rule evaluates to TRUE, its action is performed. The condition can contain Scheduler chain condition syntax or any syntax that is valid in a SQL WHERE clause. The syntax can include references to attributes of any chain step, including step completion status. A typical action is to run a specified step or to run a list of steps.

All chain rules work together to define the overall action of the chain. All rules are evaluated to see what action or actions occur next, when the chain job starts and at the end of each step. If more than one rule has a TRUE condition, multiple actions can occur. You can also cause rules to be evaluated at regular intervals by setting the evaluation_interval attribute of a chain.

Conditions are usually based on the outcome of one or more previous steps. For example, you might want one step to run if the two previous steps succeeded, and another to run if either of the two previous steps failed.

Scheduler chain condition syntax takes one of the following two forms:

stepname [NOT] {SUCCEEDED|FAILED|STOPPED|COMPLETED}
stepname ERROR_CODE {comparision_operator|[NOT] IN} {integer|list_of_integers}

You can combine conditions with boolean operators AND, OR, and NOT() to create conditional expressions. You can employ parentheses in your expressions to determine order of evaluation.

ERROR_CODE can be set with the RAISE_APPLICATION_ERROR PL/SQL statement within the program assigned to the step. Although the error codes that your program sets in this way are negative numbers, when testing ERROR_CODE in a chain rule, you test for positive numbers. For example, if your program contains the following statement:

RAISE_APPLICATION_ERROR(-20100, errmsg);

your chain rule condition must be the following:

stepname ERROR_CODE=20100

Step Attributes

The following is a list of step attributes that you can include in conditions when using SQL WHERE clause syntax:

• completed
• state
• start_date
• end_date
• error_code
• duration

The completed attribute is boolean and is TRUE when the state attribute is either SUCCEEDED, FAILED, or STOPPED.

Table 29-7 shows the possible values for the state attribute. These values are visible in the STATE column of the *_SCHEDULER_RUNNING_CHAINS views.

See the DEFINE_CHAIN_RULE procedure in Oracle Database PL/SQL Packages and Types Reference for rules and examples for SQL WHERE clause syntax.

Condition Examples Using Scheduler Chain Condition Syntax

These examples use Scheduler chain condition syntax.

Steps started by rules containing the following condition starts when the step named form_validation_step completes (SUCCEEDED, FAILED, or STOPPED).

form_validation_step COMPLETED

The following condition is similar, but indicates that the step must succeed for the condition to be met.

form_validation_step SUCCEEDED

The next condition tests for an error. It is TRUE if the step form_validation_step failed with any error code other than 20001.

form_validation_step FAILED AND form_validation_step ERROR_CODE != 20001

See the DEFINE_CHAIN_RULE procedure in Oracle Database PL/SQL Packages and Types Reference for more examples.

Condition Examples Using SQL WHERE Syntax

':step1.state=''SUCCEEDED'''

Starting the Chain

At least one rule must have a condition that always evaluates to TRUE so that the chain can start when the chain job starts. The easiest way to accomplish this is to set the condition to 'TRUE' if you are using Schedule chain condition syntax, or '1=1' if you are using SQL syntax.

Ending the Chain

At least one chain rule must contain an action of 'END'. A chain job does not complete until one of the rules containing the END action evaluates to TRUE. Several different rules with different END actions are common, some with error codes, and some without.

If a chain has no more running steps, and it is not waiting for an event to occur, and no rules containing the END action evaluate to TRUE (or there are no rules with the END action), the chain job enters the CHAIN_STALLED state. See "Handling Stalled Chains" for more information.

Example of Defining Rules

The following example defines a rule that starts the chain at step1 and a rule that starts step2 when step1 completes. rule_name and comments are optional and default to NULL. If you do use rule_name, you can later redefine that rule with another call to DEFINE_CHAIN_RULE. The new definition overwrites the previous one.

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'TRUE',
   action       =>   'START step1',
   rule_name    =>   'my_rule1',
   comments     =>   'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'step1 completed',
   action       =>   'START step2',
   rule_name    =>   'my_rule2');
END;
/

You can also configure a chain to have Scheduler evaluate its rules at a repeating time interval, such as once per hour. This capability is useful to start chain steps based on time of day or based on occurrences external to the chain. Here are some examples:

• A chain step is resource-intensive and must therefore run at off-peak hours. You could condition the step on both the completion of another step and on the time of day being after 6:00 p.m and before midnight. The Scheduler would then have to evaluate rules every so often to determine when this condition becomes TRUE.

• A step must wait for data to arrive in a table from some other process that is external to the chain. You could condition this step on both the completion of another step and on a particular table containing rows. The Scheduler would then have to evaluate rules every so often to determine when this condition becomes TRUE. The condition would use SQL WHERE clause syntax, and would be similar to the following:

  ':step1.state=''SUCCEEDED'' AND select count(*) from oe.sync_table > 0'
  

To set an evaluation interval for a chain, you set the evaluation_interval attribute when you create the chain. The data type for this attribute is INTERVAL DAY TO SECOND.

BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => INTERVAL '30' MINUTE,
   comments            => 'Chain with 30 minute evaluation interval');
END;
/

This example enables chain my_chain1:

BEGIN
  DBMS_SCHEDULER.ENABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ENABLE procedure.

The job action must refer to a previously created chain name, as shown in the following example:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name        => 'chain_job_1',
   job_type        => 'CHAIN',
   job_action      => 'my_chain1',
   repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',
   enabled         => TRUE);
END;
/

For every step of a chain job that is running, the Scheduler creates a step job with the same job name and owner as the chain job. Each step job additionally has a job subname to uniquely identify it. You can view the job subname as a column in the views *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, and *_SCHEDULER_JOB_RUN_DETAILS. The job subname is normally the same as the step name except in the following cases:

• For nested chains, the current step name may have already been used as a job subname. In this case, the Scheduler appends '_N' to the step name, where N is an integer that results in a unique job subname.

• If there is a failure when creating a step job, the Scheduler logs a FAILED entry in the job log views (*_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS) with the job subname set to 'step_name_0'.

The following example drops the chain named my_chain1:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN (
   chain_name   => 'my_chain1',
   force        => TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN procedure.

If you already created a chain job for a chain, you can use the RUN_JOB procedure to run that job (and thus run the chain), but you must set the use_current_session argument of RUN_JOB to FALSE.

You can use the RUN_CHAIN procedure to run a chain without having to first create a chain job for the chain. You can also use RUN_CHAIN to run only part of a chain.

RUN_CHAIN creates a temporary job to run the specified chain. If you supply a job name, the job is created with that name, otherwise a default job name is assigned.

If you supply a list of start steps, only those steps are started when the chain begins running. (Steps that would normally have started do not run if they are not in the list.) If no list of start steps is given, the chain starts normally—that is, an initial evaluation is done to see which steps to start running. The following example immediately runs my_chain1:

BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
   chain_name    =>  'my_chain1',
   job_name      =>  'partial_chain_job',
   start_steps   =>  'my_step2, my_step4');
END;
/

The following example drops my_rule1:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   rule_name    =>   'my_rule1',
   force        =>   TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_RULE procedure.

The following example disables my_chain1:

BEGIN
  DBMS_SCHEDULER.DISABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DISABLE procedure.

The following example drops my_step2 from my_chain2:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN_STEP (
   chain_name   =>   'my_chain2',
   step_name    =>   'my_step2',
   force        =>    TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_STEP procedure.

When you stop a chain job, all steps of the chain that are running are stopped and the chain ends.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the STOP_JOB procedure.

For each step being stopped, you must specify the schema name, chain job name, and step job subname.

BEGIN
  DBMS_SCHEDULER.STOP_JOB('oe.chainrunjob.stepa');
END;
/

In this example, chainrunjob is the chain job name and stepa is the step job subname. The step job subname is typically the same as the step name, but not always. You can obtain the step job subname from the STEP_JOB_SUBNAME column of the *_SCHEDULER_RUNNING_CHAINS views.

When you stop a chain step, its state is set to STOPPED, and the chain rules are evaluated to determine the steps to run next.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the STOP_JOB procedure.

Pausing chain steps enables you to suspend the running of the chain after those steps run.

When you pause a step, after the step runs, its state attribute changes to PAUSED, and its completed attribute remains FALSE. Therefore, steps that depend on the completion of the paused step are not run. If you reset the PAUSE attribute to FALSE for a paused step, its state attribute is set to its completion state (SUCCEEDED, FAILED, or STOPPED), and steps that are awaiting the completion of the paused step can then run.

Figure 29-1 Chain with Step 3 Paused

Description of "Figure 29-1 Chain with Step 3 Paused"

In Figure 29-1, Step 3 is paused. Until Step 3 is unpaused, Step 5 will not run. If you were to pause only Step 2, then Steps 4, 6, and 7 would not run. However Steps 1, 3, and 5 could run. In either case, you are suspending only one branch of the chain.

To pause an entire chain, you pause all steps of the chain. To unpause a chain, you unpause one, many, or all of the chain steps. With the chain in Figure 29-1, pausing Step 1 pauses the entire chain after Step 1 runs.

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

Skipping steps is especially useful when testing chains. For example, when testing the chain shown in Figure 29-1, skipping Step 7 could shorten testing time considerably, because this step is a nested chain.

There are two ways to run only a part of a chain:

• Use the ALTER_CHAIN procedure to set the PAUSE attribute to TRUE for one or more steps, and then either start the chain job with RUN_JOB or start the chain with RUN_CHAIN. Any steps that depend on the paused steps do not run, but the paused steps do run.

  The disadvantage of this method is that you must set the PAUSE attribute back to FALSE for the affected steps for future runs of the chain.

• Use the RUN_CHAIN procedure to start only certain steps of the chain, skipping those steps that you do not want to run.

  This is a more straightforward approach, which also allows you to set the initial state of steps before starting them.

You may have to use both of these methods to skip steps both at the beginning and end of a chain.

See the discussion of the RUN_CHAIN procedure in Oracle Database PL/SQL Packages and Types Reference for more information.

The *_SCHEDULER_RUNNING_JOBS views contain one row for the chain job and one row for each running step. The *_SCHEDULER_RUNNING_CHAINS views contain one row for each chain step, including any nested chains, and include run status for each step such as NOT_STARTED, RUNNING, STOPPED, SUCCEEDED, and so on.

When a chain is stalled, no steps are running, no steps are scheduled to run (after waiting a designated time interval), and no event steps are waiting for an event. The chain can make no further progress unless you manually intervene. In this case, the state of the job that is running the chain is set to CHAIN_STALLED. However, the job is still listed in the *_SCHEDULER_RUNNING_JOBS views.

You can troubleshoot a stalled chain with the views ALL_SCHEDULER_RUNNING_CHAINS, which shows the state of all steps in the chain (including any nested chains), and ALL_SCHEDULER_CHAIN_RULES, which contains all the chain rules.

You can enable the chain to continue by altering the state of one of its steps with the ALTER_RUNNING_CHAIN procedure. For example, if step 11 is waiting for step 9 to succeed before it can start, and if it makes sense to do so, you can set the state of step 9 to 'SUCCEEDED'.

Alternatively, if one or more rules are incorrect, you can use the DEFINE_CHAIN_RULE procedure to replace them (using the same rule names), or to create new rules. The new and updated rules apply to the running chain and all future chain runs. After adding or updating rules, you must run EVALUATE_RUNNING_CHAIN on the stalled chain job to trigger any required actions.

For example, the following statement creates an incompatibility named incompat1 specifying that only one of the jobs named job1, job2, or job3 can be running at the same time:

BEGIN
dbms_scheduler.create_incompatibility(
  incompatibility_name => 'icompat1', 
  object_name => 'job1,job2,job3',
  enabled => true );
END;
/

object_name contains a comma separated list of either all programs or all jobs that are incompatible with each other (that is, they cannot be run at the same time). In case of jobs the list, must consist of two or more jobs and constraint_level must be ‘JOB_LEVEL’ (the default, and not included in the example). In case of programs, constraint_level can be either ‘JOB_LEVEL’ or ‘PROGRAM_LEVEL’. When set to the default value ‘JOB_LEVEL’, only a single job that is based on the program (or programs) mentioned in object_name can run at the same time. When set to ‘PROGRAM_LEVEL’. the programs are incompatible, but the jobs based on the same program are not incompatible.

For example, if the value of object_name is ‘P1,P2,P3’ and constraint_level is ‘PROGRAM_LEVEL’, manyjobs based on P1 can be running at the same time, but if any P1 based job is running, none based on P2 or P3 can be running. Or similarly, many jobs based on P3 can be running at the same time, but none based on P1 or P2. If constraint_level is set to ‘JOB_LEVEL’, then only a single job out of all the jobs based on programs P1, P2, and P3 can be running at any given time.

For example, the following statement adds job job1 to the incompatibility named icomp1234:

BEGIN
dbms_scheduler.add_to_incompatibility(
  incompatibility_name => 'icomp1234',
  object_name => 'job1');
END;
/

incompatibility_name is the name of an existing incompatibility definition.

object_name contains a comma separated list of jobs or programs.

This procedure does not raise an error if a specified job or program to be added is already included in the specified incompatibility definition.

For example, the following statement removes job job1 from the incompatibility named icomp1234:

BEGIN
dbms_scheduler.remove_from_incompatibility(
  incompatibility_name => 'icomp1234',
  object_name => 'job1');
END;
/

incompatibility_name is the name of an existing incompatibility definition.

object_name contains a comma separated list of jobs or programs.

This procedure does not raise an error if a specified job or program to be removed does not exist in the specified incompatibility definition.

For example, the following statement drops the incompatibility named icomp1234:

BEGIN
dbms_scheduler.drop_incompatibility(
  incompatibility_name => 'icomp1234';
END;
/

incompatibility_name is the name of an existing incompatibility definition.

Resources are created in the schema of the user creating the resource.

For example, the following statement creates a resource named my_resource specifying that three units of the resource are to be made available initially, and that the Scheduler is manage the constraint so that no more than 3 units can be in use simultaneously by jobs.

BEGIN
   DBMS_SCHEDULER.CREATE_RESOURCE(
      resource_name => 'my_resource',
      units         => 3,
      state         => 'ENFORCE_CONSTRAINTS',
      comments      => 'Resource1'
   )
END;
/

If you no longer need a resource, you can drop it using the DROP_RESOURCE procedure in the DBMS_SCHEDULER package. For example:

BEGIN
   DBMS_SCHEDULER.DROP_RESOURCE(
      resource_name => 'my_resource',
      force         => true
   )
END;
/

If a resource is altered, the change does not affect currently running jobs that use this resource. The change goes into effect for subsequent jobs that use the resource.

You can specify the number of units of the resource that a specified job or program can use.

For example, the following statement specifies that the object named job1 can use one unit of the resource named resource1.

BEGIN
    DBME_SCHEDULER.SET_RESOURCE_CONSTRAINT(
         OBJECT_NAME   => 'job1',
         RESOURCE_NAME => 'resource1',
         UNITS         =>1);
END;
/

The object_name parameter can be the name of a program or a job, or a comma-separated list of names.

The units parameter specifies how many units of the resource this program or job can use. If units is set to 0, it means that the program or job does not use this resource anymore, and the constraint is deleted. If units is set to 0 on a resource for which there was no previous constraint, an error is generated.

If multiple constraints are defined on the same resource, the object types (job or program) must match. For example, if one or more constraints for a resource are based of jobs, and if a new constraint for a program is added for the same resource, an error is generated.

For example, the following statement changes the job priority for my_job1 to a setting of 1:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_emp_job1',
   attribute      =>   'job_priority',
   value          =>   1);
END;
/

You can verify that the attribute was changed by issuing the following statement:

SELECT JOB_NAME, JOB_PRIORITY FROM DBA_SCHEDULER_JOBS;

JOB_NAME                       JOB_PRIORITY
------------------------------ ------------
MY_EMP_JOB                                3
MY_EMP_JOB1                               1
MY_NEW_JOB1                               3
MY_NEW_JOB2                               3
MY_NEW_JOB3                               3

Overall priority of a job within the system is determined first by the combination of the resource consumer group that the job class of the job is assigned to and the current resource plan, and then by relative priority within the job class.