12.3. Common Administration Tools

The Oracle Scheduler is implemented through a PL/SQL package named DBMS_SCHEDULER. This package offers a collection of procedures that are used to create and manage Scheduler objects (jobs, programs, schedules, windows, job groups, and window groups). Each of these object types will be covered thoroughly in this chapter.

Most of the procedures in the DBMS_SCHEDULER package are specific to a certain object type. The object type can be derived from the name of the procedure. For example, the CREATE_PROGRAM procedure is obviously specific to program objects.

However, because all Scheduler objects share some common attributes, there are also procedures that work with any Scheduler object type. Because these procedures play an important role in the management of Scheduler objects, they warrant thorough coverage. However, due to their "global" nature, they will be covered in this section, separate from any specific object type.

In the following sections, you will learn about the following DBMS_SCHEDULER procedures:

• ENABLE

• DISABLE

• SET_ATTRIBUTE

• SET_ATTRIBUTE_NULL

You will also learn about any special cases that may exist within the different Scheduler object types.

12.3.1. Using the ENABLE Procedure

With the exception of schedules, all Scheduler objects have a common attribute named ENABLED. The attribute is a Boolean (TRUE or FALSE) value that identifies whether the object is eligible for use by the Scheduler.

Therefore, to be eligible for use in the Scheduler, the ENABLED attribute must be set to TRUE. By default, only schedule objects are enabled at creation time, because they cannot be disabled. All other objects will be disabled by default when they are created.

To enable an object, the DBMS_SCHEDULER.ENABLE procedure is used. The procedure accepts only one argument, NAME, which designates one of the following:

• The name of one specific object

• A comma-separated list of objects

For example, here's how to enable one specific object:

SQL> begin
  2  dbms_scheduler.enable('BACKUP_JOB'),
  3  end;
  4  /

PL/SQL procedure successfully completed.

To enable multiple objects, a comma-separated list can be passed in. Note that the entire list is enclosed in single quotes. Therefore, the list is submitted as a single parameter, as shown here:

SQL> begin
  2 dbms_scheduler.enable(
  3  'BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB'),

4  end;
  5  /

PL/SQL procedure successfully completed.

The list of objects can also contain both groups and individual objects:

SQL> begin
  2  dbms_scheduler.enable(
  3  'BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1'),
  4  end;
  5  /

PL/SQL procedure successfully completed.

There are a couple of special cases that should be noted about enabling group objects:

• When a job group is enabled, all members of that job group will be enabled.

• When a window group is enabled, only the window group object is enabled. Windows that are members of the group are not enabled.

• When a window or window group is referenced in the ENABLE procedure, it must always be prefixed with the SYS schema name as shown in the preceding example (SYS.WINDOW_GROUP_1).

12.3.2. Using the DISABLE Procedure

When a Scheduler object is disabled, it is ineligible for use by the Scheduler. Disabling a Scheduler object is accomplished by setting the object's ENABLED attribute to FALSE.

To disable an object, the DBMS_SCHEDULER.DISABLE procedure is used. This procedure accepts two parameters: NAME and FORCE. The NAME parameter designates one of the following:

• The name of one specific object

• A comma-separated list of objects

The FORCE parameter is a Boolean (TRUE or FALSE) value that tells the procedure how to handle the request if dependencies exist. The default value is FALSE.

There are two situations that could be classified as dependencies:

• A job object that references a program object is considered to be dependent on that object.

• If an instance of an object is currently running (for example, a window is open or a job is running), there may be a dependency issue.

If any dependencies are found, the value of the FORCE parameter will determine the ultimate outcome of the DISABLE procedure.

The purpose of the FORCE parameter is not to cascade the changes to dependent objects. The purpose is to make you aware of dependencies. No changes will be made to dependent objects.

The effect of the FORCE option varies between object types. The differences are listed in Table 12.1.

If an object has no dependencies, using the DISABLE procedure will disable any valid Scheduler object, regardless of the value of the FORCE parameter.

For example, use the following command to disable one specific object:

SQL> begin
  2 dbms_scheduler.disable('BACKUP_JOB'),
  3 end;
  4 /

PL/SQL procedure successfully completed.

To disable multiple objects, a comma-separated list can be passed in. Note that the entire list is enclosed in single quotes. Therefore, the list is submitted as a single parameter. In this example, the FORCE option is also set to TRUE:

SQL> begin
  2  dbms_scheduler.disable(
  3  'BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB',TRUE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

The list of objects can also contain both groups and individual objects:

SQL> begin
  2  dbms_scheduler.disable(
  3  'BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1'),
  4  end;
  5 /

PL/SQL procedure successfully completed.

There are a couple of special cases that should be noted about disabling group objects:

• Disabling a window group does not disable jobs that reference the group. However, those jobs will fail when they try to execute.

• Disabling a window group does not affect members of the group. They will continue to function normally.

12.3.3. Setting Attributes

You might be surprised to find that the DBMS_SCHEDULER package does not have an ALTER procedure of any kind. This is because Scheduler objects are collections of attributes. To make a change to an object requires setting its attributes. Therefore, to alter a Scheduler object, the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL procedures are used.

In the following sections, you will learn to use these procedures with all types of Scheduler objects.

The SET_ATTRIBUTE procedure sets an attribute for any type of Scheduler object. The SET_ATTRIBUTE_NULL procedure, on the other hand, sets any attribute to NULL for any type of Scheduler object. This is useful for "unsetting" an attribute.

The only attribute that cannot be altered (for any type of Scheduler object) is the name of the object.

When the attributes on an object are changed, Oracle will attempt to disable the object before making the changes. When the attribute has been successfully altered, Oracle will re-enable the object automatically. If the SET_ATTRIBUTE procedure fails, the object will remain disabled (and an error message is returned).

Using the SET_ATTRIBUTE procedure does not affect instances of the object that are currently executing. Changes made will only affect future instantiations of the object.

The SET_ATTRIBUTE procedure accepts three parameters:

NAME The name of the Scheduler object.

ATTRIBUTE The name of the attribute to be changed.

VALUE The new value for the attribute. The procedure is overloaded to accept a value of any applicable datatype, so no conversion is necessary when setting values for different datatypes.

The SET_ATTRIBUTE_NULL procedure accepts only two parameters:

NAME The name of the Scheduler object.

ATTRIBUTE The name of the attribute, which should be set to NULL.

In the last section, you learned that an object was considered enabled when the ENABLED attribute was set to a value of TRUE. Therefore, you can enable or disable an object by using the SET_ATTRIBUTE procedure, as shown here:

SQL> begin
  2  dbms_scheduler.set_attribute (
  3  name => 'TEST_JOB',
  4  attribute => 'ENABLED',
  5  value => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

To remove the end date from a schedule, the SET_ATTRIBUTE_NULL procedure can be used to set the attribute to NULL, as shown here:

SQL> begin
  2  dbms_scheduler.set_attribute_null (
  3  name => 'TEST_SCHEDULE',

4  attribute => 'END_DATE'),
  5  end;
  6  /
PL/SQL procedure successfully completed.