Scheduling Jobs with Oracle Scheduler

Overview of Creating Jobs

You create one or more jobs using the DBMS_SCHEDULER.CREATE_JOB or DBMS_SCHEDULER.CREATE_JOBS procedures or Enterprise Manager. You use theCREATE_JOB procedure to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. You can create multiple jobs in a single transaction using the CREATE_JOBS procedure.

You must have the CREATE JOB privilege to create a job in your own schema, and the CREATE ANY JOB privilege to create a job in any schema except SYS.

For each job being created, you specify a job type, an action, and a schedule. You can also optionally specify a credential name, a destination or destination group name, a job class, and other attributes. As soon as you enable a job, it is automatically run by the Scheduler at its next scheduled date and time. By default, jobs are disabled when created and must be enabled with DBMS_SCHEDULER.ENABLE to run. You can also set the enabledargument of the CREATE_JOB procedure to TRUE, in which case the job is ready to be automatically run, according to its schedule, as soon as you create it.

Some job attributes cannot be set with CREATE_JOB, and instead must be set with DBMS_SCHEDULER.SET_ATTRIBUTE. For example, to set the logging_levelattribute for a job, you must call SET_ATTRIBUTE after calling CREATE_JOB.

You can create a job in another schema by specifying schema.job_name. The creator of a job is, therefore, not necessarily the job owner. The job owner is the user in whose schema the job is created. The NLS environment of the job, when it runs, is the existing environment at the time the job was created.

Example 29-1 demonstrates creating a database job called update_sales, which calls a package procedure in the OPS schema that updates a sales summary table:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  ‘update_sales‘,
   job_type           =>  ‘STORED_PROCEDURE‘,
   job_action         =>  ‘OPS.SALES_PKG.UPDATE_SALES_SUMMARY‘,
   start_date         =>  ‘28-APR-08 07.00.00 PM Australia/Sydney‘,
   repeat_interval    =>  ‘FREQ=DAILY;INTERVAL=2‘, /* every other day */
   end_date           =>  ‘20-NOV-08 07.00.00 PM Australia/Sydney‘,
   auto_drop          =>   FALSE,
   job_class          =>  ‘batch_update_jobs‘,
   comments           =>  ‘My new job‘);
END;
/

Because no destination_name attribute is specified, the job runs on the originating (local) database. The job runs as the user who created the job.

The repeat_interval argument specifies that this job runs every other day until it reaches the end date and time. Another way to limit the number of times that a repeating job runs is to set its max_runs attribute to a positive number.

The job is disabled when it is created, by default. You must enable it with DBMS_SCHEDULER.ENABLE before the Scheduler will automatically run it.

Jobs are set to be automatically dropped by default after they complete. Setting the auto_drop attribute to FALSE causes the job to persist. Note that repeating jobs are not auto-dropped unless the job end date passes, the maximum number of runs (max_runs) is reached, or the maximum number of failures is reached (max_failures).

After a job is created, it can be queried using the *_SCHEDULER_JOBS views.

Specifying a Job Action and Job Schedule

Because the CREATE_JOB procedure is overloaded, there are several different ways of using it. In addition to specifying the job action and job repeat interval as job attributes as shown in Example 29-1, known as specifying the job action and job schedule inline, you can create a job that points to a program object (program) to specify the job action, a schedule object (schedule) to specify the repeat interval, or both a program and schedule. This is discussed in the following sections:

• Creating Jobs Using a Named Program

• Creating Jobs Using a Named Schedule

• Creating Jobs Using Named Programs and Schedules

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  ‘my_new_job1‘,
   program_name      =>  ‘my_saved_program‘, 
   repeat_interval   =>  ‘FREQ=DAILY;BYHOUR=12‘,
   comments          =>  ‘Daily at noon‘);
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  ‘my_lightweight_job1‘,
   program_name     =>  ‘polling_prog_n2‘,
   repeat_interval  =>  ‘FREQ=SECONDLY;INTERVAL=10‘,
   end_date         =>  ‘30-APR-09 04.00.00 AM Australia/Sydney‘,
   job_style        => ‘LIGHTWEIGHT‘,
   comments         => ‘Job that polls device n2 every 10 seconds‘);
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name                 =>  ‘my_new_job2‘, 
   job_type                 =>  ‘PLSQL_BLOCK‘,
   job_action               =>  ‘BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;‘,
   schedule_name            =>  ‘my_saved_schedule‘);
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  ‘my_new_job3‘, 
   program_name        =>  ‘my_saved_program1‘, 
   schedule_name       =>  ‘my_saved_schedule1‘);
END;
/

Specifying Job Credentials and Job Destinations

For local external jobs, remote external jobs, and remote database jobs, you must specify the credentials under which the job runs. You do so by creating a credential object and assigning it to the credential_name job attribute.

For remote external jobs and remote database jobs, you specify the job destination by creating a destination object and assigning it to thedestination_name job attribute. A job with a NULL destination_name attribute runs on the host where the job is created.

This section contains:

• Credential and Destination Tasks and Their Procedures

• Creating Credentials

• Creating Destinations

• Creating Destination Groups for Multiple-Destination Jobs

• Example: Creating a Remote Database Job

BEGIN
 DBMS_SCHEDULER.CREATE_CREDENTIAL(‘DW_CREDENTIAL‘, ‘dwuser‘, ‘dW001515‘);
END;
/

GRANT EXECUTE ON DW_CREDENTIAL TO salesuser;

BEGIN
 DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION (
  destination_name     => ‘DBHOST1_ORCLDW‘,
  agent                => ‘DBHOST1‘,
  tns_name             => ‘ORCLDW‘,
  comments             => ‘Instance named orcldw on host dbhost1.example.com‘);
END;
/

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

BEGIN
  DBMS_SCHEDULER.CREATE_GROUP(
    GROUP_NAME    => ‘all_dbs‘,
    GROUP_TYPE    => ‘DB_DEST‘,
    MEMBER        => ‘oltp_admin@orcl, orcldw1, LOCAL‘,
    COMMENTS      => ‘All databases managed by me‘);
END;
/

BEGIN
  DBMS_SCHEDULER.ADD_GROUP_MEMBER(
    GROUP_NAME    => ‘all_dbs‘,
    MEMBER        => ‘dw_admin@orcldw2‘);
END;
/

BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  ‘SALES_SUMMARY1‘, 
   job_type            =>  ‘STORED_PROCEDURE‘,
   job_action          =>  ‘SALES.SALES_REPORT1‘,
   start_date          =>  ‘15-JUL-09 11.00.00 PM Europe/Warsaw‘,
   repeat_interval     =>  ‘FREQ=DAILY‘,
   credential_name     =>  ‘DW_CREDENTIAL‘,
   destination_name    =>  ‘DBHOST1_ORCLDW‘);
END;
/

Creating Multiple-Destination Jobs

You can create a job that runs on multiple destinations, but that is managed from a single location. A typical reason to do this is to run a database maintenance job on all of the databases that you administer. Rather than create the job on each database, you create the job once and designate multiple destinations for the job. From the database where you created the job (the local database), you can monitor the state and results of all instances of the job at all locations.

To create a multiple-destination job: 

• Call the DBMS_SCHEDULER.CREATE_JOB procedure and set the destination_name attribute of the job to the name of database destination group or external destination group.

  If not all destination group members include a credential prefix (the schema), assign a default credential to the job.

  To include the local host or local database as one of the destinations on which the job runs, ensure that the keyword LOCAL is one of the members of the destination group.

To obtain a list of destination groups, submit this query:

SELECT owner, group_name, group_type, number_of_members FROM all_scheduler_groups
  WHERE group_type = ‘DB_DEST‘ or group_type = ‘EXTERNAL_DEST‘;

OWNER           GROUP_NAME      GROUP_TYPE    NUMBER_OF_MEMBERS
--------------- --------------- ------------- -----------------
DBA1            ALL_DBS         DB_DEST                       4
DBA1            ALL_HOSTS       EXTERNAL_DEST                 4

The following example creates a multiple-destination database job, using the database destination group created in Example 29-4. Because this is a system administration job, it uses a credential with system administrator privileges.

BEGIN
 DBMS_SCHEDULER.CREATE_CREDENTIAL(‘DBA_CREDENTIAL‘, ‘dba1‘, ‘sYs040533‘);
 DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  ‘MAINT_SET1‘, 
   job_type            =>  ‘STORED_PROCEDURE‘,
   job_action          =>  ‘MAINT_PROC1‘,
   start_date          =>  ‘15-JUL-09 11.00.00 PM Europe/Warsaw‘,
   repeat_interval     =>  ‘FREQ=DAILY‘,
   credential_name     =>  ‘DBA_CREDENTIAL‘,
   destination_name    =>  ‘ALL_DBS‘);
END;
/

Setting Job Arguments

After creating a job, you may need to set job arguments if:

• The inline job action is a stored procedure or other executable that requires arguments

• The job references a named program object and you want to override one or more default program arguments

• The job references a named program object and one or more of the program arguments were not assigned a default value

To set job arguments, use the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures or Enterprise Manager. SET_JOB_ANYDATA_VALUE is used for complex data types that cannot be represented as a VARCHAR2 string.

An example of a job that might need arguments is one that starts a reporting program that requires a start date and end date. The following code example sets the end date job argument, which is the second argument expected by the reporting program:

BEGIN
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => ‘ops_reports‘,
   argument_position       => 2,
   argument_value          => ‘12-DEC-03‘);
END;
/

If you use this procedure on an argument whose value has already been set, it will be overwritten. You can set argument values using either the argument name or the argument position. To use argument name, the job must reference a named program object, and the argument must have been assigned a name in the program object. If a program is inlined, only setting by position is supported. Arguments are not supported for jobs of type ‘PLSQL_BLOCK‘.

To remove a value that has been set, use the RESET_JOB_ARGUMENT procedure. This procedure can be used for both regular and ANYDATA arguments.

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

Setting Additional Job Attributes

After creating a job, you can set additional job attributes or change attribute values by using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures. You can also set job attributes with Enterprise Manager. Although many job attributes can be set with the call to CREATE_JOB, some attributes, such asdestination and credential_name, can be set only with SET_ATTRIBUTE or SET_JOB_ATTRIBUTES after the job is created.

Creating Detached Jobs

A detached job must point to a program object (program) that has its detached attribute set to TRUE.

#!/bin/sh
 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman
  trace /u01/app/oracle/backup/coldbackup.out &
exit 0

Step 2—Create the RMAN Script

Create an RMAN script that performs the cold backup and then ends the job. The script is located in $ORACLE_HOME/scripts/coldbackup.rman.

run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount
 
# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;
 
# Open database after backup
alter database open;
 
# Call notification routine to indicate job completed successfully
sql " BEGIN  DBMS_SCHEDULER.END_DETACHED_JOB_RUN(‘‘sys.backup_job‘‘, 0,
  null); END; ";
}

Step 3—Create the Job and Use a Detached Program

Submit the following PL/SQL block:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => ‘sys.backup_program‘,
    program_type   => ‘executable‘,
    program_action => ‘?/scripts/coldbackup.sh‘,
    enabled        =>  TRUE);

  DBMS_SCHEDULER.SET_ATTRIBUTE(‘sys.backup_program‘, ‘detached‘, TRUE);
 
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => ‘sys.backup_job‘,
    program_name    => ‘sys.backup_program‘,
    repeat_interval => ‘FREQ=DAILY;BYHOUR=1;BYMINUTE=0‘);

  DBMS_SCHEDULER.ENABLE(‘sys.backup_job‘);
END;
/

Creating Multiple Jobs in a Single Transaction

If you must create many jobs, you may be able to reduce transaction overhead and experience a performance gain if you use the CREATE_JOBSprocedure. Example 29-6 demonstrates how to use this procedure to create multiple jobs in a single transaction.

DECLARE
 newjob sys.job_definition;
 newjobarr sys.job_definition_array;
BEGIN
 -- Create an array of JOB_DEFINITION object types
 newjobarr := sys.job_definition_array();

 -- Allocate sufficient space in the array
 newjobarr.extend(5);

 -- Add definitions for 5 jobs
 FOR i IN 1..5 LOOP
   -- Create a JOB_DEFINITION object type
   newjob := sys.job_definition(job_name => ‘TESTJOB‘ || to_char(i),
                     job_style => ‘REGULAR‘,
                     program_name => ‘PROG1‘,
                     repeat_interval => ‘FREQ=HOURLY‘,
                     start_date => systimestamp + interval ‘600‘ second,
                     max_runs => 2,
                     auto_drop => FALSE,
                     enabled => TRUE
                    );

   -- Add it to the array
   newjobarr(i) := newjob;
 END LOOP;

 -- Call CREATE_JOBS to create jobs in one transaction
 DBMS_SCHEDULER.CREATE_JOBS(newjobarr, ‘TRANSACTIONAL‘);
END;
/

PL/SQL procedure successfully completed.

SELECT JOB_NAME FROM USER_SCHEDULER_JOBS;
 
JOB_NAME
------------------------------
TESTJOB1
TESTJOB2
TESTJOB3
TESTJOB4
TESTJOB5
 
5 rows selected.

Techniques for External Jobs

This section contains the following examples, which demonstrate some practical techniques for external jobs:

• Creating a Local External Job That Runs a DOS Command

• Creating a Local External Job and Retrieving stdout

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name             => ‘MKDIR_JOB‘,
   job_type             => ‘EXECUTABLE‘,
   number_of_arguments  => 3,
   job_action           => ‘\windows\system32\cmd.exe‘,
   auto_drop            => FALSE,
   credential_name      => ‘TESTCRED‘);

 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(‘mkdir_job‘,1,‘/c‘);
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(‘mkdir_job‘,2,‘mkdir‘);
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(‘mkdir_job‘,3,‘\temp\extjob_test_dir‘);
 DBMS_SCHEDULER.ENABLE(‘MKDIR_JOB‘);
END;
/

-- User scott must have CREATE JOB and CREATE EXTERNAL JOB privileges
grant create job, create external job to scott ;
 
connect scott/password
set serveroutput on
 
-- Create a credential for the job to use
exec dbms_scheduler.create_credential(‘my_cred‘,‘host_username‘,‘host_passwd‘)
 
-- Create a job that lists a directory. After running, the job is dropped.
begin
 DBMS_SCHEDULER.CREATE_JOB(
  job_name            => ‘lsdir‘,
  job_type            => ‘EXECUTABLE‘,
  job_action          => ‘/bin/ls‘,
  number_of_arguments => 1,
  enabled             => false,
  auto_drop           => true,
  credential_name     => ‘my_cred‘);
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(‘lsdir‘,1,‘/tmp‘);
 DBMS_SCHEDULER.ENABLE(‘lsdir‘);
end;
/
 
-- Wait a bit for the job to run, and then check the job results.
select job_name, status, error#, actual_start_date, additional_info
 from user_scheduler_job_run_details where job_name=‘LSDIR‘;
 
-- Now use the external log id from the additional_info column to
-- formulate the log file name and retrieve the output
declare
 my_blob blob;
 log_id varchar2(50);
begin
 select regexp_substr(additional_info,‘job[_0-9]*‘) into log_id
   from user_scheduler_job_run_details where job_name=‘LSDIR‘;
 dbms_lob.createtemporary(my_blob, false);
 dbms_scheduler.get_file(
   source_file     => log_id ||‘_stdout‘,
   credential_name => ‘my_cred‘,
   file_contents   => my_blob,
   source_host     => null);
 dbms_output.put_line(my_blob);
end;
/

Stopping External Jobs

The Scheduler offers implementors of external jobs a mechanism to gracefully clean up after their external jobs when STOP_JOB is called with force set to FALSE. The following applies only to local external jobs created without credentials on any platform, and 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 theSIGTERM 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 only on Windows XP, Windows 2003, and later operating systems. On those platforms, 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.

Stopping a Chain Job

If a job pointing to a chain is stopped, all steps of the running chain that are running are stopped.

See "Stopping Individual Chain Steps" for information about stopping individual chain steps.

Defining Program Arguments

After creating a program, you can define program arguments. Arguments are defined by position in the calling sequence, with an optional argument name and optional default value. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.) All argument values must be defined before the job can be enabled.

To set program argument values, use the DEFINE_PROGRAM_ARGUMENT or DEFINE_ANYDATA_ARGUMENT procedures. DEFINE_ANYDATA_ARGUMENT is used for complex types that must be encapsulated in an ANYDATA object. An example of a program that might need arguments is one that starts a reporting program that requires a start date and end date. The following code example sets the end date argument, which is the second argument expected by the reporting program. The example also assigns a name to the argument so that you can refer to the argument by name (instead of position) from other package procedures, including SET_JOB_ANYDATA_VALUE and SET_JOB_ARGUMENT_VALUE.

BEGIN
 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
   program_name            => ‘operations_reporting‘,
   argument_position       => 2,
   argument_name           => ‘end_date‘,
   argument_type           => ‘VARCHAR2‘,
   default_value           => ‘12-DEC-03‘);
END;
/

Valid values for the argument_type argument are only SQL data types, therefore booleans are not supported. For external executables, only string types such as CHAR or VARCHAR2 are permitted.

You can drop a program argument either by name or by position, as in the following:

BEGIN
 DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            => ‘operations_reporting‘,
   argument_position       => 2);

 DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            => ‘operations_reporting‘,
   argument_name           => ‘end_date‘);
END;
/

In some special cases, program logic is dependent on the Scheduler environment. The Scheduler has some predefined metadata arguments that can be passed as an argument to the program for this purpose. For example, for some jobs whose schedule is a window name, it is useful to know how much longer the window will be open when the job is started. This is possible by defining the window end time as a metadata argument to the program.

If a program needs access to specific job metadata, you can define a special metadata argument using the DEFINE_METADATA_ARGUMENT procedure, so values will be filled in by the Scheduler when the program is executed.

Using the Scheduler Calendaring Syntax

The primary method of setting how often a job will repeat is by setting the repeat_interval attribute with a Scheduler calendaring expression. SeeOracle Database PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as theCREATE_SCHEDULE procedure.

ADMIN12676Examples of Calendaring Expressions

The following examples illustrate simple repeat intervals. For simplicity, it is assumed that there is no contribution to the evaluation results by the start date.

Run every Friday. (All three examples are equivalent.)

FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

Run every other Friday.

FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

Run on the last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-1;

Run on the next to last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th. (Both examples are equivalent)

FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every 10 days.

FREQ=DAILY; INTERVAL=10;

Run daily at 4, 5, and 6PM.

FREQ=DAILY; BYHOUR=16,17,18;

Run on the 15th day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

Run on the 29th day of every month.

FREQ=MONTHLY; BYMONTHDAY=29;

Run on the second Wednesday of each month.

FREQ=MONTHLY; BYDAY=2WED;

Run on the last Friday of the year.

FREQ=YEARLY; BYDAY=-1FRI;

Run every 50 hours.

FREQ=HOURLY; INTERVAL=50;

Run on the last day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

Run hourly for the first three days of every month.

FREQ=HOURLY; BYMONTHDAY=1,2,3;

Here are some more complex repeat intervals:

Run on the last workday of every month (assuming that workdays are Monday through Friday).

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

Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called Company_Holidays.)

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

Run at noon every Friday and on company holidays.

FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays

Run on these three holidays: July 4th, Memorial Day, and Labor Day. (This example references three existing named schedules—JUL4, MEM, and LAB—where each defines a single date corresponding to a holiday.)

JUL4,MEM,LAB

ADMIN12677Examples of Calendaring Expression Evaluation

A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:

SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00
...

A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15,-1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:

WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 15-FEB-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 15-MAR-2004 09:00:00
WED 31-MAR-2004 09:00:00
...

A repeat interval of "FREQ=MONTHLY;" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule. (Note that because there is noBYMONTHDAY clause, the day of month is retrieved from the start date.)

MON 29-DEC-2003 09:00:00
THU 29-JAN-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 29-MAR-2004 09:00:00
...

ADMIN12678Example of Using a Calendaring Expression

As an example of using the calendaring syntax, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => ‘scott.my_job1‘,
   start_date           => ‘15-JUL-04 01.00.00 AM Europe/Warsaw‘,
   repeat_interval      => ‘FREQ=MINUTELY; INTERVAL=30;‘,
   end_date             => ‘15-SEP-04 01.00.00 AM Europe/Warsaw‘,
   comments             => ‘My comments here‘);
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then run until September 15. The job is run every 30 minutes.

Using a PL/SQL Expression

When you need more complicated capabilities than the calendaring syntax provides, you can use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or in named schedules. The PL/SQL expression must evaluate to a date or a timestamp. Other than this restriction, there are no limitations, so with sufficient programming, you can create every possible repeat interval. As an example, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => ‘scott.my_job2‘, 
   start_date           => ‘15-JUL-04 01.00.00 AM Europe/Warsaw‘,
   repeat_interval      => ‘SYSTIMESTAMP + INTERVAL ‘30‘ MINUTE‘,
   end_date             => ‘15-SEP-04 01.00.00 AM Europe/Warsaw‘,
   comments             => ‘My comments here‘);
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then every 30 minutes until September 15. The job is run every 30 minutes because repeat_interval is set to SYSTIMESTAMP + INTERVAL ‘30‘ MINUTE, which returns a date 30 minutes into the future.

Differences Between PL/SQL Expression and Calendaring Syntax Behavior

The following are important differences in behavior between a calendaring expression and PL/SQL repeat interval:

• Start date

  Using the calendaring syntax, the start date is a reference date only. Therefore, the schedule is valid as of this date. It does not mean that the job will start on the start date.

  Using a PL/SQL expression, the start date represents the actual time that the job will start executing for the first time.

• Next run time

  Using the calendaring syntax, the next time the job will run is fixed.

  Using the PL/SQL expression, the next time the job will run depends on the actual start time of the current run of the job. As an example of the difference, if a job started at 2:00 PM and its schedule was to repeat every 2 hours, then, if the repeat interval was specified with the calendaring syntax, it would repeat at 4, 6 and so on. If PL/SQL was used and the job started at 2:10, then the job would repeat at 4:10, and if the next job actually started at 4:11, then the subsequent run would be at 6:11.

To illustrate these two points, consider a situation where you have a start date of 15-July-2003 1:45:00 and you want it to repeat every two hours. A calendar expression of "FREQ=HOURLY; INTERVAL=2; BYMINUTE=0;" will generate the following schedule:

TUE 15-JUL-2003  03:00:00
TUE 15-JUL-2003  05:00:00
TUE 15-JUL-2003  07:00:00
TUE 15-JUL-2003  09:00:00
TUE 15-JUL-2003  11:00:00
...

Note that the calendar expression repeats every two hours on the hour.

A PL/SQL expression of "SYSTIMESTAMP + interval ‘2‘ hour", however, might have a run time of the following:

TUE 15-JUL-2003  01:45:00
TUE 15-JUL-2003  03:45:05
TUE 15-JUL-2003  05:45:09
TUE 15-JUL-2003  07:45:14
TUE 15-JUL-2003  09:45:20
...

Repeat Intervals and Daylight Savings

For repeating jobs, the next time a job is scheduled to run is stored in a timestamp with time zone column. When using the calendaring syntax, the time zone is retrieved from start_date. For more information on what happens when start_date is not specified, see Oracle Database PL/SQL Packages and Types Reference.

In the case of repeat intervals that are based on PL/SQL expressions, the time zone is part of the timestamp that is returned by the PL/SQL expression. In both cases, it is important to use region names. For example, "Europe/Istanbul", instead of absolute time zone offsets such as "+2:00". Only when a time zone is specified as a region name will the Scheduler follow daylight savings adjustments that apply to that region.

Creating an Event-Based Job

You use the CREATE_JOB procedure or Enterprise Manager to create an event-based job. The job can include event information inline as job attributes or can specify event information by pointing to an event schedule.

Like jobs based on time schedules, event-based jobs are not auto-dropped unless the job end date passes, max_runs is reached, or the maximum number of failures (max_failures) is reached.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  ‘process_lowinv_j1‘,
   program_name        =>  ‘process_lowinv_p1‘,
   event_condition     =>  ‘tab.user_data.event_type = ‘‘LOW_INVENTORY‘‘‘,
   queue_spec          =>  ‘inv_events_q, inv_agent1‘,
   enabled             =>  TRUE,
   comments            =>  ‘Start an inventory replenishment job‘);
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  ‘process_lowinv_j1‘,
   program_name        =>  ‘process_lowinv_p1‘,
   schedule_name       =>  ‘inventory_events_schedule‘,
   enabled             =>  TRUE,
   comments            =>  ‘Start an inventory replenishment job‘);
END;
/

Altering an Event-Based Job

You alter an event-based job by using the SET_ATTRIBUTE procedure. For jobs that specify the event inline, you cannot set the queue_spec andevent_condition attributes individually with SET_ATTRIBUTE. Instead, you must set an attribute called event_spec, and pass an event condition and queue specification as the third and fourth arguments, respectively, to SET_ATTRIBUTE.

The following is an example of using the event_spec attribute:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (‘my_job‘, ‘event_spec‘, 
   ‘tab.user_data.event_type = ‘‘LOW_INVENTORY‘‘‘, ‘inv_events_q, inv_agent1‘);
END;
/

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

Creating an Event Schedule

You can create a schedule that is based on an event. You can then reuse the schedule for multiple jobs. To do so, use the CREATE_EVENT_SCHEDULEprocedure, or use Enterprise Manager. The following is an example of creating an event schedule:

BEGIN
  DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  ‘inventory_events_schedule‘,
   start_date        =>  SYSTIMESTAMP,
   event_condition   =>  ‘tab.user_data.event_type = ‘‘LOW_INVENTORY‘‘‘, 
   queue_spec        =>  ‘inv_events_q, inv_agent1‘);
END;
/

You can drop an event schedule using the DROP_SCHEDULE procedure. See Oracle Database PL/SQL Packages and Types Reference for more information on CREATE_EVENT_SCHEDULE.

Altering an Event Schedule

You alter the event information in an event schedule in the same way that you alter event information in a job. For more information, see "Altering an Event-Based Job".

The following example demonstrates how to use the SET_ATTRIBUTE procedure and the event_spec attribute to alter event information in an event schedule.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (‘inventory_events_schedule‘, ‘event_spec‘,
   ‘tab.user_data.event_type = ‘‘LOW_INVENTORY‘‘‘, ‘inv_events_q, inv_agent1‘);
END;
/

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

Passing Event Messages into an Event-Based Job

Through a metadata argument, the Scheduler can pass to an event-based job the message content of the event that started the job. The following rules apply:

• The job must use a named program of type STORED_PROCEDURE.

• One of the named program‘s arguments must be a metadata argument with metadata_attribute set to EVENT_MESSAGE.

• The stored procedure that implements the program must have an argument at the position corresponding to the named program‘s metadata argument. The argument type must be the data type of the queue where your application queues the job-start event.

If you use the RUN_JOB procedure to manually run a job that has an EVENT_MESSAGE metadata argument, the value passed to that argument is NULL.

The following example shows how to construct an event-based job that can receive the event message content:

create or replace procedure my_stored_proc (event_msg IN event_queue_type)
as
begin
  -- retrieve and process message body
end;
/ 
 
begin
  dbms_scheduler.create_program (
      program_name => ‘my_prog‘,
      program_action=> ‘my_stored_proc‘,
      program_type => ‘STORED_PROCEDURE‘,
      number_of_arguments => 1,
      enabled => FALSE) ;
 
  dbms_scheduler.define_metadata_argument (
      program_name => ‘my_prog‘,
      argument_position => 1 ,
      metadata_attribute => ‘EVENT_MESSAGE‘) ;
 
  dbms_scheduler.enable (‘my_prog‘);
exception
  when others then raise ;
end ;
/
 
begin
  dbms_scheduler.create_job (
     job_name => ‘my_evt_job‘ ,
     program_name => ‘my_prog‘,
     schedule_name => ‘my_evt_sch‘,
     enabled => true,
     auto_Drop => false) ;
exception
  when others then raise ;
end ;
/

About File Watchers

A file watcher is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.

A file watcher can watch for a file on the local system (the same host computer running Oracle Database) or a remote system. Remote systems must be running the Scheduler agent, and the agent must be registered with the database.

File watchers check for the arrival of files every 10 minutes. You can adjust this interval. See "Changing the File Arrival Detection Interval" for details.

You must have the CREATE JOB system privilege to create a file watcher in your own schema. You require the CREATE ANY JOB system privilege to create a file watcher in a schema different from your own (except the SYS schema, which is disallowed). You can grant the EXECUTE object privilege on a file watcher so that jobs in different schemas can reference it. You can also grant the ALTER object privilege on a file watcher so that another user can modify it.

To use file watchers, the database Java virtual machine (JVM) component must be installed.

Enabling File Arrival Events from Remote Systems

To receive file arrival events from a remote system, you must install the Scheduler agent on that system, and you must register the agent with the database. The remote system does not require a running Oracle Database instance to generate file arrival events.

To enable the raising of file arrival events at remote systems: 

1. Set up the local database to run remote external jobs.

   See "Setting up Databases for Remote Jobs" for instructions.

2. Install, configure, register, and start the Scheduler agent on the first remote system.

   See "Installing and Configuring the Scheduler Agent on a Remote Host" for instructions.

   This adds the remote host to the list of external destinations maintained on the local database.

3. Repeat the previous step for each additional remote system.

Creating File Watchers and File Watcher Jobs

You perform the following tasks to create a file watcher and create the event-based job that starts when the designated file arrives.

ADMIN13478

Task 1   - Create a Credential

The file watcher requires a Scheduler credential object (a credential) with which to authenticate with the host operating system for access to the file. See "Credentials" for information on privileges required to create credentials.

Perform these steps:

1. Create a credential for the operating system user that must have access to the watched-for file.

   BEGIN
     DBMS_SCHEDULER.CREATE_CREDENTIAL(‘WATCH_CREDENTIAL‘, ‘salesapps‘, ‘sa324w1‘);
   END;
   /
   

2. Grant the EXECUTE object privilege on the credential to the schema that owns the event-based job that the file watcher will start.

   GRANT EXECUTE ON WATCH_CREDENTIAL to DSSUSER;
   

ADMIN13479

Task 2   - Create a File Watcher

Perform these steps:

1. Create the file watcher, assigning attributes as described in the DBMS_SCHEDULER.CREATE_FILE_WATCHER procedure documentation in Oracle Database PL/SQL Packages and Types Reference. You can specify wildcard parameters in the file name. A ‘?‘ prefix in the DIRECTORY_PATHattribute denotes the path to the Oracle home directory. A NULL destination indicates the local host. To watch for the file on a remote host, provide a valid external destination name, which you can obtain from the view ALL_SCHEDULER_EXTERNAL_DESTS.

   BEGIN
     DBMS_SCHEDULER.CREATE_FILE_WATCHER(
       FILE_WATCHER_NAME => ‘EOD_FILE_WATCHER‘,
       DIRECTORY_PATH    => ‘?/eod_reports‘,
       FILE_NAME         => ‘eod*.txt‘,
       CREDENTIAL_NAME   => ‘WATCH_CREDENTIAL‘,
       DESTINATION       => NULL,
       ENABLED           => FALSE);
   END;
   /
   

2. Grant EXECUTE on the file watcher to any schema that owns an event-based job that references the file watcher.

   GRANT EXECUTE ON EOD_FILE_WATCHER to DSSUSER;
   

ADMIN13480

Task 3   - Create a Program Object with a Metadata Argument

So that your application can retrieve the file arrival event message content, which includes file name, file size, and so on, create a Scheduler program object with a metadata argument that references the event message.

Perform these steps:

1. Create the program.

   BEGIN
     DBMS_SCHEDULER.CREATE_PROGRAM(
       PROGRAM_NAME        => ‘DSSUSER.EOD_PROGRAM‘,
       PROGRAM_TYPE        => ‘STORED_PROCEDURE‘,
       PROGRAM_ACTION      => ‘EOD_PROCESSOR‘,
       NUMBER_OF_ARGUMENTS => 1,
       ENABLED             => FALSE);
   END;
   /
   

2. Define the metadata argument using the event_message attribute.

   BEGIN
     DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(
       PROGRAM_NAME       => ‘DSSUSER.EOD_PROGRAM‘,
       METADATA_ATTRIBUTE => ‘event_message‘,
       ARGUMENT_POSITION  => 1);
   END;
   /
   

3. Create the stored procedure that the program invokes.

   The stored procedure that processes the file arrival event must have an argument of type SYS.SCHEDULER_FILEWATCHER_RESULT, which is the data type of the event message. The position of that argument must match the position of the defined metadata argument. The procedure can then access attributes of this abstract data type to learn about the arrived file.

See Also:

• Oracle Database PL/SQL Packages and Types Reference for a description of the DEFINE_METADATA_ARGUMENTprocedure

• Oracle Database PL/SQL Packages and Types Reference for a description of theSYS.SCHEDULER_FILEWATCHER_RESULT type

ADMIN13481

Task 4   - Create an Event-Based Job That References the File Watcher

Create the event-based job as described in "Creating an Event-Based Job", with the following exception: instead of providing a queue specification in the queue_spec attribute, provide the name of the file watcher. You would typically leave the event_condition job attribute null, but you can provide a condition if desired.

As an alternative to setting the queue_spec attribute for the job, you can create an event schedule, reference the file watcher in the queue_specattribute of the event schedule, and reference the event schedule in the schedule_name attribute of the job.

Perform these steps to prepare the event-based job:

1. Create the job.

   BEGIN
     DBMS_SCHEDULER.CREATE_JOB(
       JOB_NAME        => ‘DSSUSER.EOD_JOB‘,
       PROGRAM_NAME    => ‘DSSUSER.EOD_PROGRAM‘,
       EVENT_CONDITION => NULL,
       QUEUE_SPEC      => ‘EOD_FILE_WATCHER‘,
       AUTO_DROP       => FALSE,
       ENABLED         => FALSE);
   END;
   /
   

2. If you want the job to run for each instance of the file arrival event, even if the job is already processing a previous event, set theparallel_instances attribute to TRUE. With this setting, the job runs as a lightweight job so that multiple instances of the job can be started quickly. To discard file watcher events that occur while the event-based job is already processing another, leave the parallel_instancesattribute FALSE (the default).

   BEGIN
     DBMS_SCHEDULER.SET_ATTRIBUTE(‘DSSUSER.EOD_JOB‘,‘PARALLEL_INSTANCES‘,TRUE);
   END;
   /
   

   For more information about this attribute, see the SET_ATTRIBUTE description in Oracle Database PL/SQL Packages and Types Reference.

See Also:

• "Creating an Event Schedule"

• "Creating Jobs Using Named Programs and Schedules"

ADMIN13482

Task 5   - Enable All Objects

Enable the file watcher, the program, and the job.

BEGIN
   DBMS_SCHEDULER.ENABLE(‘DSSUSER.EOD_PROGRAM,DSSUSER.EOD_JOB,EOD_FILE_WATCHER‘);
END;
/

File Arrival Example

In this example, an event-based job watches for the arrival of end-of-day sales reports onto the local host from various locations. As each report file arrives, a stored procedure captures information about the file and stores the information in a table called eod_reports. A regularly scheduled report aggregation job can then query this table, process all unprocessed files, and mark any newly processed files as processed.

It is assumed that the database user running the following code has been granted EXECUTE on the SYS.SCHEDULER_FILEWATCHER_RESULT data type.

begin
  dbms_scheduler.create_credential(
     credential_name => ‘watch_credential‘,
     username        => ‘pos1‘,
     password        => ‘jk4545st‘);
end;
/
 
create table eod_reports (when timestamp, file_name varchar2(100), 
   file_size number, processed char(1));
 
create or replace procedure q_eod_report 
  (payload IN sys.scheduler_filewatcher_result) as 
begin
  insert into eod_reports values 
     (payload.file_timestamp,
      payload.directory_path || ‘/‘ || payload.actual_file_name,
      payload.file_size,
      ‘N‘);
end;
/
 
begin
  dbms_scheduler.create_program(
    program_name        => ‘eod_prog‘,
    program_type        => ‘stored_procedure‘,
    program_action      => ‘q_eod_report‘,
    number_of_arguments => 1,
    enabled             => false);
  dbms_scheduler.define_metadata_argument(
    program_name        => ‘eod_prog‘,
    metadata_attribute  => ‘event_message‘,
    argument_position   => 1);
  dbms_scheduler.enable(‘eod_prog‘);
end;
/
 
begin
  dbms_scheduler.create_file_watcher(
    file_watcher_name => ‘eod_reports_watcher‘,
    directory_path    => ‘?/eod_reports‘,
    file_name         => ‘eod*.txt‘,
    credential_name   => ‘watch_credential‘,
    destination       => null,
    enabled           => false);
end;
/
 
begin
  dbms_scheduler.create_job(
    job_name        => ‘eod_job‘,
    program_name    => ‘eod_prog‘,
    event_condition => ‘tab.user_data.file_size > 10‘,
    queue_spec      => ‘eod_reports_watcher‘,
    auto_drop       => false,
    enabled         => false);
  dbms_scheduler.set_attribute(‘eod_job‘,‘parallel_instances‘,true);
end;
/
 
exec dbms_scheduler.enable(‘eod_reports_watcher,eod_job‘);

Managing File Watchers

The DBMS_SCHEDULER PL/SQL package provides procedures for enabling, disabling, dropping, and setting attributes for file watchers.

The section contains:

• Enabling File Watchers

• Altering File Watchers

• Disabling and Dropping File Watchers

• Changing the File Arrival Detection Interval

Viewing File Watcher Information

You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.

SELECT file_watcher_name, destination, directory_path, file_name, credential_name 
   FROM dba_scheduler_file_watchers;

FILE_WATCHER_NAME    DESTINATION          DIRECTORY_PATH       FILE_NAME  CREDENTIAL_NAME
-------------------- -------------------- -------------------- ---------- ----------------
MYFW                 dsshost.example.com  /tmp                 abc        MYFW_CRED
EOD_FILE_WATCHER                          ?/eod_reports        eod*.txt   WATCH_CREDENTIAL

Setting an Evaluation Interval for Chain Rules

The Scheduler evaluates all chain rules at the start of the chain job and at the end of each chain step. You can configure a chain to also have its rules evaluated 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 INTERVALDAY 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;
/

Job Class Tasks and Their Procedures

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

See "Scheduler Privileges" for further information regarding privileges.

Creating Job Classes

You create a job class using the CREATE_JOB_CLASS procedure or Enterprise Manager. Job classes are always created in the SYS schema.

The following statement creates a job class for all finance jobs:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name             =>  ‘finance_jobs‘, 
   resource_consumer_group    =>  ‘finance_group‘);
END;
/

All jobs in this job class are assigned to the finance_group resource consumer group.

To query job classes, use the *_SCHEDULER_JOB_CLASSES views.

Altering Job Classes

You alter a job class by using the SET_ATTRIBUTE procedure or Enterprise Manager. Other than the job class name, all the attributes of a job class can be altered. The attributes of a job class are available in the *_SCHEDULER_JOB_CLASSES views.

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

Dropping Job Classes

You drop one or more job classes using the DROP_JOB_CLASS procedure or Enterprise Manager. Dropping a job class means that all the metadata about the job class is removed from the database.

You can drop several job classes in one call by providing a comma-delimited list of job class names to the DROP_JOB_CLASS procedure call. For example, the following statement drops three job classes:

BEGIN
  DBMS_SCHEDULER.DROP_JOB_CLASS(‘jobclass1, jobclass2, jobclass3‘);
END;
/

Window Tasks and Their Procedures

Table 29-9 illustrates common window tasks and the procedures you use to handle them.

See "Scheduler Privileges" for further information regarding privileges.

Creating Windows

You can use Enterprise Manager or the DBMS_SCHEDULER.CREATE_WINDOW package procedure to create windows. When using the package procedure, you can leave the resource_plan parameter NULL. In this case, when the window opens, the current plan remains in effect.

You must have the MANAGE SCHEDULER privilege to create windows.

When you specify a schedule for a window, the Scheduler does not check if there is already a window defined for that schedule. Therefore, this may result in windows that overlap. Also, using a named schedule that has a PL/SQL expression as its repeat interval is not supported for windows

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

The following example creates a window named daytime that enables the mixed_workload_plan resource plan during office hours:

BEGIN
   DBMS_SCHEDULER.CREATE_WINDOW (
     window_name      => ‘daytime‘,
     resource_plan    => ‘mixed_workload_plan‘,
     start_date       => ‘28-APR-09 08.00.00 AM‘,
     repeat_interval  => ‘freq=daily; byday=mon,tue,wed,thu,fri‘,
     duration         => interval ‘9‘ hour,
     window_priority  => ‘low‘,
     comments         => ‘OLTP transactions have priority‘);
END;
/

To verify that the window was created properly, query the view DBA_SCHEDULER_WINDOWS. As an example, issue the following statement:

SELECT WINDOW_NAME, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME   RESOURCE_PLAN         DURATION       REPEAT_INTERVAL
-----------   -------------------   -------------  ---------------
DAYTIME       MIXED_WORKLOAD_PLAN   +000 09:00:00  freq=daily; byday=mon,tue,wed,thu,fri

Altering Windows

You alter a window by modifying its attributes. You do so with the SET_ATTRIBUTE and SET_ATTRIBUTE_NULL procedures or Enterprise Manager. With the exception of WINDOW_NAME, all the attributes of a window can be changed when it is altered. See the CREATE_WINDOW procedure in Oracle Database PL/SQL Packages and Types Reference for window attribute details.

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

All windows can be altered. If you alter a window that is disabled, it will remain disabled after it is altered. An enabled window will be automatically disabled, altered, and then reenabled, if the validity checks performed during the enable process are successful.

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

Opening Windows

When a window opens, the Scheduler switches to the resource plan that has been associated with it during its creation. If there are jobs running when the window opens, the resources allocated to them might change due to the switch in resource plan.

There are two ways a window can open:

• According to the window‘s schedule

• Manually, using the OPEN_WINDOW procedure

  This procedure opens the window independent of its schedule. This window will open and the resource plan associated with it will take effect immediately. Only an enabled window can be manually opened.

  In the OPEN_WINDOW procedure, you can specify the time interval that the window should be open for, using the duration attribute. The duration is of type interval day to second. If the duration is not specified, then the window will be opened for the regular duration as stored with the window.

  Opening a window manually has no impact on regular scheduled runs of the window.

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

  You can force a window to open even if there is one already open by setting the force option to TRUE in the OPEN_WINDOW call or Enterprise Manager.

  When the force option is set to TRUE, 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. You can open a window that is already open. In this case, the window stays open for the duration specified in the call, from the time the OPEN_WINDOWcommand was issued.

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

When a window opens, an entry is made in the window log.

A window can fail to switch resource plans if the current resource plan has been manually switched using the ALTER SYSTEM statement with the FORCEoption, or using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure with the allow_scheduler_plan_switches argument set to FALSE. In this case, the failure to switch resource plans is written to the window log.

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

Closing Windows

There are two ways a window can close:

• Based on a schedule

  A window will close based on the schedule defined at creation time.

• Manually, using the CLOSE_WINDOW procedure

  The CLOSE_WINDOW procedure will close an open window prematurely.

A closed window means that it is no longer in effect. When a window is closed, the Scheduler will switch the resource plan to the one that was in effect outside the window or in the case of overlapping windows to another window. If you try to close a window that does not exist or is not open, an error is generated.

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

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

When a window is closed, an entry will be added to the window log DBA_SCHEDULER_WINDOW_LOG.

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

Dropping Windows

You drop one or more windows using the DROP_WINDOW procedure or Enterprise Manager. When a window is dropped, all metadata about the window is removed from the *_SCHEDULER_WINDOWS views. All references to the window are removed from window groups.

You can drop several windows in one call by providing a comma-delimited list of window names or window group names to the DROP_WINDOW procedure. For example, the following statement drops both windows and window groups:

BEGIN
  DBMS_SCHEDULER.DROP_WINDOW (‘window1, window2, window3, 
   windowgroup1, windowgroup2‘);
END;
/

Note that if a window group name is provided, then the windows in the window group are dropped, but the window group is not dropped. To drop the window group, you must use the DROP_WINDOW_GROUP procedure.

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

Disabling Windows

You disable one or more windows using the DISABLE procedure or with Enterprise Manager. Therefore, the window will not open. However, the metadata of the window is still there, so it can be reenabled. Because the DISABLE procedure is used for several Scheduler objects, when disabling windows, they must be preceded by SYS.

A window can also become disabled for other reasons. For example, a window will become disabled when it is at the end of its schedule. Also, if a window points to a schedule that no longer exists, it becomes disabled.

If there are jobs that have the window as their schedule, you will not be able to disable the window unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the window is disabled, those jobs that have the window as their schedule will not be disabled.

You can disable several windows in one call by providing a comma-delimited list of window names or window group names to the DISABLE procedure call. For example, the following statement disables both windows and window groups:

BEGIN
  DBMS_SCHEDULER.DISABLE (‘sys.window1, sys.window2, 
   sys.window3, sys.windowgroup1, sys.windowgroup2‘);
END;
/

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

Enabling Windows

You enable one or more windows using the ENABLE procedure or Enterprise Manager. An enabled window is one that can be opened. Windows are, by default, created enabled. When a window is enabled using the ENABLE procedure, a validity check is performed and only if this is successful will the window be enabled. When a window is enabled, it is logged in the window log table. Because the ENABLE procedure is used for several Scheduler objects, when enabling windows, they must be preceded by SYS.

You can enable several windows in one call by providing a comma-delimited list of window names. For example, the following statement enables three windows:

BEGIN
  DBMS_SCHEDULER.ENABLE (‘sys.window1, sys.window2, sys.window3‘);
END;
/

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

Window Group Tasks and Their Procedures

Table 29-10 illustrates common window group tasks and the procedures you use to handle them.

See "Scheduler Privileges" for further information regarding privileges.

Creating Window Groups

You create a window group by using the DBMS_SCHEDULER.CREATE_GROUP procedure, specifying a group type of ‘WINDOW‘. You can specify the member windows of the group when you create the group, or you can add them later using the ADD_GROUP_MEMBER procedure. A window group cannot be a member of another window group. You can, however, create a window group that has no members.

If you create a window group and you specify a member window that does not exist, an error is generated and the window group is not created. If a window is already a member of a window group, it is not added again.

Window groups are created in the SYS schema. Window groups, like windows, are created with access to PUBLIC, therefore, no privileges are required to access window groups.

The following statement creates a window group called downtime and adds two windows (weeknights and weekends) to it:

BEGIN
  DBMS_SCHEDULER.CREATE_GROUP (
   group_name   =>  ‘downtime‘,
   group_type   =>  ‘WINDOW‘,
   member       =>  ‘weeknights, weekends‘);
END;
/

To verify the window group contents, issue the following queries as a user with the MANAGE SCHEDULER privilege:

SELECT group_name, enabled, number_of_members FROM dba_scheduler_groups
  WHERE group_type = ‘WINDOW‘;

GROUP_NAME     ENABLED  NUMBER_OF_MEMBERS
-------------- -------- -----------------
DOWNTIME       TRUE                     2

SELECT group_name, member_name FROM dba_scheduler_group_members;

GROUP_NAME      MEMBER_NAME
--------------- --------------------
DOWNTIME        "SYS"."WEEKENDS"
DOWNTIME        "SYS"."WEEKNIGHTS"

Dropping Window Groups

You drop one or more window groups by using the DROP_GROUP procedure. This call will drop the window group but not the windows that are members of this window group. To drop all the windows that are members of this group but not the window group itself, you can use the DROP_WINDOW procedure and provide the name of the window group to the call.

You can drop several window groups in one call by providing a comma-delimited list of window group names to the DROP_GROUP procedure call. You must precede each window group name with the SYS schema. For example, the following statement drops three window groups:

BEGIN
DBMS_SCHEDULER.DROP_GROUP(‘sys.windowgroup1, sys.windowgroup2, sys.windowgroup3‘);
END;
/

Adding a Member to a Window Group

You add windows to a window group by using the ADD_GROUP_MEMBER procedure.

You can add several members to a window group in one call, by specifying a comma-delimited list of windows. For example, the following statement adds two windows to the window group window_group1:

BEGIN
  DBMS_SCHEDULER.ADD_GROUP_MEMBER (‘sys.windowgroup1‘,‘window2, window3‘);
END;
/

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

Removing a Member from a Window Group

You can remove one or more windows from a window group by using the REMOVE_GROUP_MEMBER procedure. Jobs with the stop_on_window_close flag set will only be stopped when a window closes. Dropping an open window from a window group has no impact on this.

You can remove several members from a window group in one call by specifying a comma-delimited list of windows. For example, the following statement drops two windows:

BEGIN
  DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(‘sys.window_group1‘, ‘window2, window3‘);
END;
/

Enabling a Window Group

You enable one or more window groups using the ENABLE procedure. By default, window groups are created ENABLED. For example:

BEGIN
  DBMS_SCHEDULER.ENABLE(‘sys.windowgroup1, sys.windowgroup2, sys.windowgroup3‘);
END;
/

Disabling a Window Group

You disable a window group using the DISABLE procedure. A job with a disabled window group as its schedule does not run when the member windows open. Disabling a window group does not disable its member windows.

You can also disable several window groups in one call by providing a comma-delimited list of window group names. For example, the following statement disables three window groups:

BEGIN
  DBMS_SCHEDULER.DISABLE(‘sys.windowgroup1, sys.windowgroup2, sys.windowgroup3‘);
END;
/

Run Details

For every row in *_SCHEDULER_JOB_LOG for which the operation is RUN, RETRY_RUN, or RECOVERY_RUN, there is a corresponding row in the*_SCHEDULER_JOB_RUN_DETAILS view. Rows from the two different views are correlated with their LOG_ID columns. You can consult the run details views to determine why a job failed or was stopped.

SELECT to_char(log_date, ‘DD-MON-YY HH24:MI:SS‘) TIMESTAMP, job_name, status,
   SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
   FROM user_scheduler_job_run_details ORDER BY log_date;

TIMESTAMP            JOB_NAME   STATUS    ADDITIONAL_INFO
-------------------- ---------- --------- ----------------------------------------
18-DEC-07 23:12:41   JOB2       SUCCEEDED
18-DEC-07 23:12:18   JOB2       STOPPED   REASON="Stop job called by user:‘SYSTEM‘
19-DEC-07 14:12:20   REMOTE_16  FAILED    ORA-29273: HTTP request failed ORA-06512

The run details views also contain actual job start times and durations.

Precedence of Logging Levels in Jobs and Job Classes

Both jobs and job classes have a logging_level attribute, with possible values listed in Table 29-11. The default logging level for job classes isLOGGING_RUNS, and the default level for individual jobs is LOGGING_OFF. If the logging level of the job class is higher than that of a job in the class, then the logging level of the job class takes precedence. Thus, by default, all job runs are recorded in the job log.

For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off or set logging to occur only when jobs fail. However, you might prefer to have a complete audit trail of everything that happens with jobs in a specific class, in which case you would enable full logging for that class.

To ensure that there is an audit trail for all jobs, the individual job creator must not be able to turn logging off. The Scheduler supports this by making the class-specified level the minimum level at which job information is logged. A job creator can only enable more logging for an individual job, not less. Thus, leaving all individual job logging levels set to LOGGING_OFF ensures that all jobs in a class get logged as specified in the class.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and logging is turned off at the job level, the Scheduler still logs job runs. If, however, the job creator turns on full logging and the class-specific level is set to record runs only, the higher logging level of the job takes precedence and all operations on this individual job are logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the SET_ATTRIBUTE procedure on that job. For example, to turn on full logging for a job calledmytestjob, issue the following statement:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   ‘mytestjob‘, ‘logging_level‘, DBMS_SCHEDULER.LOGGING_FULL);
END;
/

Only a user with the MANAGE SCHEDULER privilege can set the logging level of a job class.

About Job State Events

You can configure a job so that the Scheduler raises an event when the job changes state. The Scheduler can raise an event when a job starts, when a job completes, when a job exceeds its allotted run time, and so on. The consumer of the event is your application, which takes some action in response to the event. For example, if due to a high system load, a job is still not started 30 minutes after its scheduled start time, the Scheduler can raise an event that causes a handler application to stop lower priority jobs to free up system resources. The Scheduler can raise job state events for local (regular) jobs, remote database jobs, local external jobs, and remote external jobs.

Table 29-13 describes the job state event types raised by the Scheduler.

You enable the raising of job state events by setting the raise_events job attribute. By default, a job does not raise any job state events.

The Scheduler uses Oracle Streams Advanced Queuing to raise events. When raising a job state change event, the Scheduler enqueues a message onto a default event queue. Your applications subscribe to this queue, dequeue event messages, and take appropriate actions.

After you enable job state change events for a job, the Scheduler raises these events by enqueuing messages onto the Scheduler event queueSYS.SCHEDULER$_EVENT_QUEUE. This queue is a secure queue, so depending on your application, you may have to configure the queue to enable certain users to perform operations on it. See Oracle Streams Concepts and Administration for information on secure queues.

To prevent unlimited growth of the Scheduler event queue, events raised by the Scheduler expire in 24 hours by default. (Expired events are deleted from the queue.) You can change this expiry time by setting the event_expiry_time Scheduler attribute with the SET_SCHEDULER_ATTRIBUTE procedure. SeeOracle Database PL/SQL Packages and Types Reference for more information.

Altering a Job to Raise Job State Events

To enable job state events to be raised for a job, you use the SET_ATTRIBUTE procedure to turn on bit flags in the raise_events job attribute. Each bit flag represents a different job state to raise an event for. For example, turning on the least significant bit enables job started events to be raised. To enable multiple state change event types in one call, you add the desired bit flag values together and supply the result as an argument to SET_ATTRIBUTE.

The following example enables multiple state change events for job dw_reports. It enables the following event types, both of which indicate some kind of error.

• JOB_FAILED

• JOB_SCH_LIM_REACHED

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(‘dw_reports‘, ‘raise_events‘,
   DBMS_SCHEDULER.JOB_FAILED + DBMS_SCHEDULER.JOB_SCH_LIM_REACHED);
END;
/

Consuming Job State Events with your Application

To consume job state events, your application must subscribe to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE. This queue is a secure queue and is owned by SYS. To create a subscription to this queue for a user, do the following:

1. Log in to the database as the SYS user or as a user with the MANAGE ANY QUEUE privilege.

2. Subscribe to the queue using a new or existing agent.

3. Run the package procedure DBMS_AQADM.ENABLE_DB_ACCESS as follows:

   DBMS_AQADM.ENABLE_DB_ACCESS(agent_name, db_username);
   

   where agent_name references the agent that you used to subscribe to the events queue, and db_username is the user for whom you want to create a subscription.

There is no need to grant dequeue privileges to the user. The dequeue privilege is granted on the Scheduler event queue to PUBLIC.

As an alternative, the user can subscribe to the Scheduler event queue using the ADD_EVENT_QUEUE_SUBSCRIBER procedure, as shown in the following example:

DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER(subscriber_name);

where subscriber_name is the name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. (If it isNULL, an agent is created whose name is the user name of the calling user.) This call both creates a subscription to the Scheduler event queue and grants the user permission to dequeue using the designated agent. The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. After the subscription is in place, the user can either poll for messages at regular intervals or register with AQ for notification.

See Oracle Streams Advanced Queuing User‘s Guide for more information.

ADMIN12679Scheduler Event Queue

The Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE is of type scheduler$_event_info. See Oracle Database PL/SQL Packages and Types Referencefor details on this type.

About E-mail Notifications

You can configure a job to send e-mail notifications when it changes state. The job state events for which e-mails can be sent are listed in Table 29-13. E-mail notifications can be sent to multiple recipients, and can be triggered by any event in a list of job state events that you specify. You can also provide a filter condition, and only job state events that match the filter condition generate notifications. You can include variables like job owner, job name, event type, error code, and error message in both the subject and body of the message. The Scheduler automatically sets values for these variables before sending the e-mail notification.

You can configure many job state e-mail notifications for a single job. The notifications can differ by job state event list, recipients, and filter conditions.

For example, you can configure a job to send an e-mail to both the principle DBA and one of the senior DBAs whenever the job fails with error code 600 or 700. You can also configure the same job to send a notification to only the principle DBA if the job fails to start at its scheduled time.

Before you can configure jobs to send e-mail notifications, you must set the Scheduler attribute email_server to the address of the SMTP server to use to send the e-mail. You may also optionally set the Scheduler attribute email_sender to a default sender e-mail address for those jobs that do not specify a sender.

The Scheduler includes support for the SSL and TLS protocols when communicating with the SMTP server. The Scheduler also supports SMTP servers that require authentication.

Adding E-mail Notifications for a Job

You use the DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION package procedure to add e-mail notifications for a job.

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name   =>  ‘EOD_JOB‘,
  recipients =>  ‘jsmith@example.com, rjones@example.com‘,
  sender     =>  ‘do_not_reply@example.com‘,
  subject    =>  ‘Scheduler Job Notification-%job_owner%.%job_name%-%event_type%‘,
  body       =>   ‘%event_type% occurred at %event_timestamp%. %error_message%‘,
  events     =>  ‘JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED‘);
END;
/

Note the variables, enclosed in the ‘%‘ character, used in the subject and body arguments. When you specify multiple recipients and multiple events, each recipient is notified when any of the specified events is raised. You can verify this by querying the view USER_SCHEDULER_NOTIFICATIONS.

SELECT JOB_NAME, RECIPIENT, EVENT FROM USER_SCHEDULER_NOTIFICATIONS;

JOB_NAME    RECIPIENT            EVENT
----------- -------------------- -------------------
EOD_JOB     jsmith@example.com   JOB_FAILED
EOD_JOB     jsmith@example.com   JOB_BROKEN
EOD_JOB     jsmith@example.com   JOB_SCH_LIM_REACHED
EOD_JOB     jsmith@example.com   JOB_DISABLED
EOD_JOB     rjones@example.com   JOB_FAILED
EOD_JOB     rjones@example.com   JOB_BROKEN
EOD_JOB     rjones@example.com   JOB_SCH_LIM_REACHED
EOD_JOB     rjones@example.com   JOB_DISABLED

You call ADD_JOB_EMAIL_NOTIFICATION once for each different set of notifications that you want to configure for a job. You must specify job_name andrecipients. All other arguments have defaults. The default sender is defined by a Scheduler attribute, as described in the previous section. See theADD_JOB_EMAIL_NOTIFICATION procedure in Oracle Database PL/SQL Packages and Types Reference for defaults for the subject, body, and eventsarguments.

The following example configures an additional e-mail notification for the same job for a different event. This example accepts the defaults for thesender, subject, and body arguments.

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name         =>  ‘EOD_JOB‘,
  recipients       =>  ‘jsmith@example.com‘,
  events           =>  ‘JOB_OVER_MAX_DUR‘);
END;
/

This example could have also omitted the events argument to accept event defaults.

The next example is similar to the first, except that it uses a filter condition to specify that an e-mail notification is to be sent only when the error number that causes the job to fail is 600 or 700.

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name         => ‘EOD_JOB‘,
  recipients       => ‘jsmith@example.com, rjones@example.com‘,
  sender           => ‘do_not_reply@example.com‘,
  subject          => ‘Job Notification-%job_owner%.%job_name%-%event_type%‘,
  body             =>  ‘%event_type% at %event_timestamp%. %error_message%‘,
  events           => ‘JOB_FAILED‘,
  filter_condition => ‘:event.error_code=600 or :event.error_code=700‘);
END;
/

Removing E-mail Notifications for a Job

You use the DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION package procedure to remove e-mail notifications for a job.

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  ‘EOD_JOB‘,
  recipients =>  ‘jsmith@example.com, rjones@example.com‘,
  events     =>  ‘JOB_DISABLED, JOB_SCH_LIM_REACHED‘);
END;
/

When you specify multiple recipients and multiple events, the notification for each specified event is removed for each recipient. Running the same query as that of the previous section, the results are now the following:

SELECT JOB_NAME, RECIPIENT, EVENT FROM USER_SCHEDULER_NOTIFICATIONS;

JOB_NAME    RECIPIENT            EVENT
----------- -------------------- -------------------
EOD_JOB     jsmith@example.com   JOB_FAILED
EOD_JOB     jsmith@example.com   JOB_BROKEN
EOD_JOB     rjones@example.com   JOB_FAILED
EOD_JOB     rjones@example.com   JOB_BROKEN

Additional rules for specifying REMOVE_JOB_EMAIL_NOTIFICATION arguments are as follows:

• If you leave the events argument NULL, notifications for all events for the specified recipients are removed.

• If you leave recipients NULL, notifications for all recipients for the specified events are removed.

• If you leave both recipients and events NULL, then all notifications for the job are removed.

• If you include a recipient and event for which you did not previously create a notification, no error is generated.

Viewing Information About E-mail Notifications

As demonstrated in the previous sections, you can view information about current e-mail notifications by querying the views *_SCHEDULER_NOTIFICATIONS.