Oracle Scheduler

Oracle Scheduler is a feature of Oracle database. As such it is superior to many ad-hoc schedulers as it automatically provides programmability based on capabilities of Oracle SQL.  Program manager for this product is Vira Goorah.

It enables users to schedule jobs running inside the database such as PL/SQL procedures or PL/SQL blocks as well as jobs running outside the database such as shell scripts.

Oracle 10g introduced the concept of external jobs. Oracle 11g takes this one step further by allowing the database to schedule external jobs which run on a remote server. The remote server doesn't have to have an Oracle client or database installation, but it must have an Oracle Scheduler Agent installation. This agent is responsible for executing the jobs and communicating with the database server that initiated the job. ( http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR1.php )

There are two interfaces for the Oracle Scheduler:

• Oracle API (DBMS_SCHEDULER package).
• UI which is part of Oracle Enterprise Manager (looks like that reveals just a tiny part of functionality: only calendar scheduling)

It is a free feature of the database. Oracle Scheduler  is an Oracle application implemented via the procedures and functions in the DBMS_SCHEDULER PL/SQL package.

• Run external programs. You can run external executables on the local system or on remote systems. Remote systems do not require an Oracle Database installation; they require only a Scheduler agent. Scheduler agents are available for all platforms supported by Oracle Database and some additional platforms.
  • Run PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures.
  • Run executables that are external to the database (external executables), such as applications, shell scripts, and batch files.
• Schedule job execution using the following methods:
   
  • Time-based scheduling You can schedule a job to run at a particular date and time, either once or on a repeating basis. You can define complex repeat intervals, such as "every Monday and Thursday at 3:00a.m except on public holidays" or "the last Wednesday of each business quarter."
  • Event-based scheduling The Scheduler enables you to start jobs in response to system or business events. Your applications can detect events and then signal the Scheduler. Depending on the type of signal sent, the Scheduler starts a specific job. Examples of event-based scheduling include starting jobs when a file arrives on a system, when inventory falls below predetermined levels, or when a transaction fails.
  • Dependency scheduling The Scheduler can run tasks based on the outcome of one or more previous tasks. You can define complex dependency chains that include branching and nested chains.
• Prioritize jobs based on business requirements.

  The Scheduler enables control over resource allocation among competing jobs, thus aligning job processing with your business needs. This is accomplished in the following ways:

  • Jobs that share common characteristics and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This enables you to ensure that your critical jobs have priority and have enough resources to complete. For example, if you have a critical project to load a data warehouse, then you can combine all the data warehousing jobs into one class and give priority to it over other jobs by allocating it a high percentage of the available resources.
  • The Scheduler takes prioritization of jobs one step further, by providing you the ability to change the prioritization based on a schedule. Because your definition of a critical job can change over time, the Scheduler enables you to also change the priority among your jobs over that time frame. For example, you may consider the extract, transfer, and load (ETL) jobs used to load a data warehouse to be critical jobs during non-peak hours but not during peak hours. However, jobs that must run during the close of a business quarter may need to take priority over the ETL jobs. In these cases, you can change the priority among the job classes by changing the resource allocated to each class. See "Creating Job Classes" and "Creating Windows" for more information.
     
• Manage and monitor jobs

  There are multiple states that a job undergoes from its creation to its completion. Scheduler activity is logged and information such as the status of the job and the last run time of the job can be easily tracked. This information is stored in views and can be easily queried using Enterprise Manager or SQL. These views provide valuable information about jobs and their execution that can help you schedule and better manage your jobs. For example, a DBA can easily track all jobs that failed for a particular user. See "Monitoring and Managing the Scheduler".
   

• Execute and manage jobs in a clustered environment

  A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the database service where you want a job to run. See "Using the Scheduler in Real Application Clusters Environments" for more information.

Scheduler Objects

To use the Scheduler, you create Scheduler objects. These are schema objects that define the what, when, and how for job scheduling. Scheduler objects enable a modular approach to managing tasks. One advantage of the modular approach is that objects can be reused when creating new tasks that are similar to existing tasks.

All Scheduler objects have attributes. You assign values to these attributes when you create or modify the objects.

The Scheduler objects include:

• Programs
• Schedules
• Jobs
• Chains
• Job Classes
• Windows
• Window Groups

Each of these objects is described in detail in other sections of this documentation. The principal Scheduler object is the job. The job defines the action to perform and the schedule by which to perform it. It does so either in a stand-alone manner or by referencing other Scheduler objects.

Because Scheduler objects belong to schemas, you can grant object privileges on them. Some Scheduler objects, including job classes, windows, and window groups, are always created in the SYS schema, even if the creating user is not user SYS. All other objects are created in the schema of the creating user or in the designated schema.

Technical Information

• Join the Oracle Scheduler Customer Advisory Board
• Oracle Scheduler Overview
• Oracle Scheduler Technical Whitepaper (PDF)
• Oracle Scheduler Frequently Asked Questions (FAQ)

Supported platforms

• Supported: Solaris64, Linux, HP64, TRU64, IA64 HP, IA64 Linux, Solaris x86, MAC, z/Linux, AMD, Windows.
• Planned: VMS
• Not supported: OS390, AIX

Contents

Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-03

A job is the combination of a schedule and a program, along with any additional arguments required by the program. This section introduces you to basic job tasks, and discusses the following topics:

• Job Tasks and Their Procedures
• Creating Jobs
• Copying Jobs
• Altering Jobs
• Running Jobs
• Stopping Jobs
• Dropping Jobs
• Disabling Jobs
• Enabling Jobs

Job Tasks and Their Procedures

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

See "Scheduler Privileges" for further information regarding privileges.

Creating Jobs

You create one or more jobs using the CREATE_JOB or CREATE_JOBS procedures or Enterprise Manager. The CREATE_JOB procedure is used to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. Multiple jobs can be created using the CREATE_JOBS procedure.

For each job being created, you specify a job type, an action, a schedule, and other attributes. The job type specifies whether to create a regular job or a lightweight job. If you do specify a job type, the default type is regular.

For example, the following statement creates a single job called update_sales, which calls a stored 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-03 07.00.00 PM Australia/Sydney',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */
   end_date           =>  '20-NOV-04 07.00.00 PM Australia/Sydney',
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/

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, while the job creator is the user who is creating the job. Jobs are executed with the privileges of the schema in which the job is created. The NLS environment of the job when it runs is that which was present at the time the job was created.

After a job is created, it can be queried using the *_SCHEDULER_JOBS views. Jobs are created disabled by default and need to be enabled to run.

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

Setting Job Attributes

You can set job attributes when creating the job, or you can set them after the job is created by using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. (Some job attributes can be set only after the job is created.)

When you set the COMMIT_SEMANTICS parameter of a job to TRANSACTIONAL or ABSORB_ERRORS, you can perform multiple operations within the scope of a single transaction.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and about the various job attributes.

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 must be encapsulated in an ANYDATA object.

Note:

• The SET_JOB_ARGUMENT_VALUE procedure can be used to set arguments of lightweight jobs but only if the arguments are of type VARCHAR2.

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.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_JOB_ARGUMENT_VALUE and SET_JOB_ANYDATA_VALUE procedures.

Ways of Creating Jobs

Because the CREATE_JOB procedure is overloaded, there are several different ways of using it. In addition to inlining a job during the job creation, you can also create a job that points to a named 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 a Named Program and Schedule

Creating Jobs Using a Named Program

You can create a job by pointing to a named program instead of inlining its action. To create a job using a named program, you specify the value for program_name in the CREATE_JOB procedure when creating the job and do not specify the values for job_type, job_action, and number_of_arguments.

To use an existing program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privileges on it. An example of using the CREATE_JOB procedure with a named program is the following statement, which creates a regular job called my_new_job1:

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;
/

The following statement creates a lightweight job that uses the program MY_PROG as a job template.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'my_lightweight_job1',
   program_name     =>  'MY_PROG',
   repeat_interval  =>  'FREQ=DAILY;BY_HOUR=9',
   end_time         =>  '30-APR-07 04.00.00 AM Australia/Sydney',
   job_style        => 'LIGHTWEIGHT',
   comments         => 'New lightweight job based on a program');
END;
/

Creating Jobs Using a Named Schedule

You can also create a job by pointing to a named schedule instead of inlining its schedule. To create a job using a named schedule, you specify the value for schedule_name in the CREATE_JOB procedure when creating the job and do not specify the values for start_date, repeat_interval, and end_date.

You can use any named schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a named schedule is the following statement, which creates a regular job called my_new_job2:

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;
/

Creating Jobs Using a Named Program and Schedule

A job can also be created by pointing to both a named program and schedule. An example of using the CREATE_JOB procedure with a named program and schedule is the following statement, which creates a regular job called my_new_job3 based on the existing program my_saved_program1 and the existing schedule my_saved_schedule1:

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

The following statement creates a lightweight job that is based on the existing program MY_PROG and the existing schedule MY_SCHED.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_lightweight_job2', 
   program_name        =>  'my_prog', 
   schedule_name       =>  'my_sched',
   job_style           =>  'LIGHTWEIGHT');
END;
/

Creating Remote External Jobs

The CREATE JOB and CREATE EXTERNAL JOB privileges are both required for any schema that creates remote external jobs.

To create a remote external job:

1. Create the job using the CREATE_JOB procedure.
2. Create a credential using the CREATE_CREDENTIAL procedure of the DBMS_SCHEDULER package.

   The following example creates a credential named NICKID, which consists of the username NICK and the password firesign:

   SQL> EXEC DBMS_SCHEDULER.CREATE_CREDENTIAL('NICKID', 'NICK', 'firesign');
   

3. Set the credential_name attribute of the job using the SET_ATTRIBUTE procedure.

   The job owner must have EXECUTE privileges on the credential or be the owner of the credential.

4. Set the destination attribute of the job using the SET_ATTRIBUTE procedure.

   The attribute must be of the form host:port, where host is the host name or IP address of the remote host, and port is the port on which the Scheduler agent on that host listens. To determine this port number, view the file schagent.conf, which is located in the Scheduler agent home directory on the remote host.

5. Enable the job using the ENABLE_JOB procedure.

Example 1

The following example creates a remote external job named CLEANLOGS that uses a credential named LOGOWNER. The destination host and port number are app455 and 12345.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name             => 'CLEANLOGS',
   job_type             => 'EXECUTABLE',
   job_action           => '/home/logowner/cleanlogs',
   repeat_interval      => 'FREQ=DAILY; BYHOUR=23',
   enabled              => FALSE);
DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'credential_name', 'LOGOWNER');
DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'destination', 'app455:12345');
DBMS_SCHEDULER.ENABLE('CLEANLOGS');
END;
/

Example 2

The following example creates the same remote external job for multiple remote hosts. The PL/SQL code includes a loop that iterates over the host names. remote_cred is the name of a credential that is valid on all hosts. The list of destinations is a list of host names and Scheduler agent ports. The executable being run on all hosts is the application /u01/app/ext_backup.

declare
job_prefix varchar2(30) := 'remote_';
job_name varchar2(30);
destinations dbms_utility.lname_array;
begin
 
   destinations(1) := 'host1:1234';
   destinations(2) := 'host2:1234';
   destinations(3) := 'host3:1234';
   destinations(4) := 'host4:1234';
 
  for i in 1..destinations.LAST loop
    job_name := dbms_scheduler.generate_job_name(job_prefix);
    dbms_scheduler.create_job(job_name,
    job_type=>'executable',
    job_action=>'/u01/app/ext_backup',
    number_of_arguments=>0,
    enabled=>false);
 
    dbms_scheduler.set_attribute(job_name,'destination',destinations(i));
    dbms_scheduler.set_attribute(job_name,'credential_name','remote_cred');
    dbms_scheduler.enable(job_name);
  end loop;
end;
/

Example 3

The example illustrates how a remote external job can submit SQL statements to a remote Oracle database. The job action runs a shell script that uses SQL*Plus to submit the statements. The script must reside on the remote host. The script, shown below, starts by setting all environment variables required to run SQL*Plus on Linux.

To avoid hard-coding a database password in the script, external authentication is used.

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

# The following command assumes external authentication
$ORACLE_HOME/bin/sqlplus / << EOF
set serveroutput on;
select * from dual;
EXIT;
EOF

See Also:

• Oracle Database Security Guide for more information about external authentication
• "About Remote External Jobs"
• "Capturing Standard Error Output for External Jobs"
• "Stopping External Jobs"

Copying Jobs

You copy a job using the COPY_JOB procedure or Enterprise Manager. This call copies all the attributes of the old job to the new job except the new job is created disabled and has another name.

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

Altering Jobs

You alter a job using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. All jobs can be altered, and, with the exception of 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 perfectly valid for running jobs to alter their own job attributes, however, these changes will not be picked up until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and "Configuring Oracle Scheduler".

Running Jobs

Normally, jobs are executed asynchronously. The user creates a job and the API immediately returns and indicates whether the creation was successful. To find out whether the job succeeded, the user has to query the job table or the job log. While this is the expected behavior, for special cases, the database also allows users to run jobs synchronously.

Running Jobs Asynchronously

You can schedule a job to run asynchronously based on the schedule defined when the job is created. In this case, the job is submitted to the job coordinator and is picked up by the job slaves for execution.

Running Jobs Synchronously

After a job has been created, you can run the job synchronously using the RUN_JOB procedure with the use_current_session argument set to TRUE. In this case, the job will run within the user session that invoked the RUN_JOB call instead of being picked up by the coordinator and being executed by a job slave.

You can use the RUN_JOB procedure to test a job or to run it outside of its specified schedule. Running a job with RUN_JOB with the use_current_session argument set to TRUE does not change the count for failure_count and run_count for the job. The job run will, however, be reflected in the job log. Runtime errors generated by the job are passed back to the invoker of RUN_JOB.

When using RUN_JOB to run a job that points to a chain, use_current_session must be set to FALSE.

Job Run Environment

Jobs are run with the privileges that are granted to the job owner directly or indirectly through default logon roles. External OS roles are not supported. Given sufficient privileges, users can create jobs in other users' schemas. The creator and the owner of the job can, therefore, be different. For example, if user jim has the CREATE ANY JOB privilege and creates a job in the scott schema, then the job will run with the privileges of scott.

The NLS environment of the session in which the job was created is saved and is used when the job is being executed. To alter the NLS environment in which a job runs, a job must be created in a session with different NLS settings.

Capturing Standard Error Output for External Jobs

When a local external job or remote external job writes output to stderr, the first 200 bytes are recorded in the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views. The information is in the following name/value pair format:

STANDARD_ERROR="text"

Note:

To retrieve the entire standard error text, you can use the DBMS_SCHEDULER.GET_FILE procedure. See Oracle Database PL/SQL Packages and Types Reference for detailed information about this procedure.

Stopping Jobs

You stop one or more running jobs using the STOP_JOB procedure or Enterprise Manager. STOP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all running jobs in the job class are stopped. For example, the following statement stops job job1 and all jobs in the job class dw_jobs.

BEGIN
DBMS_SCHEDULER.STOP_JOB('job1, sys.dw_jobs');
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 runtime statistics might not be available for the job run.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous stop operations that were successful are committed to disk. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to stop the rest of the jobs and commits all the stop operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

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

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

Caution:

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. On Unix, this is done by sending a SIGTERM signal to the process launched by the Scheduler agent. 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 only on Windows XP, Windows 2003, and later operating systems. On those platforms, the process launched by the Scheduler agent 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.

Dropping Jobs

You drop one or more jobs using the DROP_JOB procedure or Enterprise Manager. 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.

For example, 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;
/

Dropping a job results in the job being removed from the job table, its metadata being removed, and it no longer being visible in the *_SCHEDULER_JOBS views. Therefore, no more runs of the job will be executed.

If an instance of the job is running at the time of the DROP_JOB call, the call results in an error. You can still drop the job by setting the force option in the call to TRUE. Setting the force option to TRUE first attempts to stop the running job instance by using an interrupt mechanism (by calling STOP_JOB with the force option set to FALSE), and then drops the job.

Alternatively, you can call STOP_JOB to first stop the job and then call DROP_JOB to drop it. If you have the MANAGE SCHEDULER privilege, you can call STOP_JOB with force, if the regular STOP_JOB call failed to stop the job, and then call DROP_JOB.

By default, force is set to FALSE.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous drop operations that were successful 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 the previous drop 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 drop the rest of the jobs and commits all the drops that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

The DROP_JOB_CLASS procedure should be used to drop a job class. See "Dropping Job Classes" for information about how to drop job classes.

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

Disabling Jobs

You disable one or more jobs using the DISABLE procedure or Enterprise Manager. A job can also become disabled for other reasons. For example, a job will be disabled when the job class it belongs to is dropped. A job is also disabled if either the program or the schedule that it points to is dropped. Note that if the program or schedule that the job points to is disabled, the job will not be disabled and will therefore result in an error when the Scheduler tries to run the job.

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

When a job is disabled with the force option set to FALSE and the job is currently running, an error is returned. 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 disable operations that were successful 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 the previous disable 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 disable the rest of the jobs and commits all the disable operations that were successful. 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.

Enabling Jobs

You enable one or more jobs by using the ENABLE procedure or Enterprise Manager. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to 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 commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous enable operations that were successful 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 enable operations that were successful. 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.

Using Programs

A program is a collection of metadata about a particular task. This section introduces you to basic program tasks, and discusses the following topics:

• Program Tasks and Their Procedures
• Creating Programs
• Altering Programs
• DroppingPrograms
• Disabling Programs
• Enabling Programs

See Also:

Program Tasks and Their Procedures

Table 27-2 illustrates common program tasks and their appropriate procedures and privileges:

Table 27-2 Program Tasks and Their Procedures

See "Scheduler Privileges" for further information regarding privileges.

Creating Programs

You create programs by using the CREATE_PROGRAM procedure or Enterprise Manager. By default, programs are created in the schema of the creator. To create a program in another user's schema, you need to qualify the program name with the schema name. For other users to use your programs, they must have EXECUTE privileges on the program, therefore, once a program has been created, you have to grant the EXECUTE privilege on it. An example of creating a program is the following, which creates a program called my_program1:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'my_program1',
   program_action         => '/usr/local/bin/date',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;
/

Defining Program Arguments

After creating a program, you can define a name or default value for each program argument. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.) 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;
/

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.

• See Also:

  Oracle Database PL/SQL Packages and Types Reference for detailed information about the DEFINE_PROGRAM_ARGUMENT, DEFINE_ANYDATA_ARGUMENT, and DEFINE_METADATA_ARGUMENT procedures

Altering Programs

You can use Enterprise Manager or the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL package procedures to alter programs. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_SCHEDULER package procedures. The following are instructions for altering a program with Enterprise Manager:

1. Access the Database Home page.
2. At the top of the page, click Server to display the Server page.
3. In the Oracle Scheduler section, click Programs

   The Scheduler Programs page appears. It displays existing programs.

4. Select a program, and then click Edit.

   The Edit Program page appears.

5. Next to the Enabled heading, select Yes or No.
6. In the Description field, change any comments.
7. From the Type drop-down list, select one of the following:
   • PLSQL_BLOCK

     A Source field appears. Enter or alter the PL/SQL code in this field.

   • STORED_PROCEDURE

     A Procedure Name field appears. If the field contains a stored procedure name, click View Procedure to view or edit the stored procedure. If the field is blank, or if you want to change stored procedures, click Select Procedure. A Select Procedure page then appears. Select a stored procedure and then click Select to return to the Edit Program page. (Click Help at the top of the page for help with using the Select Procedure page.)

     With a procedure name selected, a list of arguments appears under the Arguments heading on the Edit Program page. Optionally enter default values for one or more arguments.

   • EXECUTABLE

     An Executable Name field appears. Enter the full path of the executable. Under the Arguments heading, edit or delete arguments, or click Add Another Row to add an argument.

8. Click Apply to save your changes.

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

Dropping Programs

You drop one or more programs using the DROP_PROGRAM procedure or Enterprise Manager.

Running jobs that point to the program are not affected by the DROP_PROGRAM call, and are allowed to continue. Any arguments that pertain to the program are also dropped when the program is 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;
/

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

Disabling Programs

You disable one or more programs using the DISABLE procedure or Enterprise Manager. 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.

Running jobs that point to the program are not affected by the DISABLE call, and are allowed to continue. Any argument that pertains to the program will not be affected when the program is disabled.

A program can also become disabled for other reasons. For example, if a program argument is dropped or number_of_arguments is changed so that all arguments are no longer defined.

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

Enabling Programs

You enable one or more programs using the ENABLE procedure or Enterprise Manager. 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.

Using Schedules

A schedule defines when a job should be run or when a window should open. Schedules can be shared among users by creating and saving them as objects in the database.

This section introduces you to basic schedule tasks, and discusses the following topics:

• Schedule Tasks and Their Procedures
• Creating Schedules
• Altering Schedules
• Dropping Schedules
• Setting the Repeat Interval

See Also:

Schedule Tasks and Their Procedures

Table 27-3 illustrates common schedule tasks and the procedures you use to handle them.

Table 27-3 Schedule Tasks and Their Procedures

See "Scheduler Privileges" for further information regarding privileges.

Creating Schedules

You create schedules by using the CREATE_SCHEDULE procedure or Enterprise Manager. 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. An example of creating a schedule is the following statement:

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;
/

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

Altering Schedules

You alter a schedule by using the SET_ATTRIBUTE procedure or Enterprise Manager. 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 will not affect running jobs and open windows that use this schedule. The change will only be in 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.

Dropping Schedules

You drop a schedule using the DROP_SCHEDULE procedure or Enterprise Manager. This procedure call will delete the schedule object from the database.

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

Setting the Repeat Interval

You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager.

The result of evaluating the repeat_interval is a set of timestamps. The Scheduler runs the job at each timestamp. Note that the start date from the job or schedule also helps determine the resulting set of timestamps. (See Oracle Database PL/SQL Packages and Types Reference for more information about repeat_interval evaluation.) If no value for repeat_interval is specified, the job runs only once at the specified start date.

Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job. It is possible that the next scheduled execution time arrives while the job is still running. A new instance of the job, however, will not be started until the current one completes.

There are two ways to specify the repeat interval:

• Using the Scheduler Calendaring Syntax
• Using a PL/SQL Expression

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. See Oracle Database PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure.

Examples 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

Examples 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 no BYMONTHDAY 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
...

Example 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. This means that 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.

Using Job Classes

Jobs classes provide a way to group jobs for resource allocation and prioritization, and a way to easily assign a set of attribute values to member jobs.

There is a default job class that is created with the database. If you create a job without specifying a job class, the job will be assigned to this default job class (DEFAULT_JOB_CLASS). The default job class has the EXECUTE privilege granted to PUBLIC so any database user who has the privilege to create a job can create a job in the default job class.

This section introduces you to basic job class tasks, and discusses the following topics:

• Job Class Tasks and Their Procedures
• Creating Job Classes
• Altering Job Classes
• Dropping Job Classes

See Also:

Job Class Tasks and Their Procedures

Table 27-4 illustrates common job class tasks and their appropriate procedures and privileges:

Table 27-4 Job Class Tasks and Their Procedures

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. For example, 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;
/

To query job classes, use the *_SCHEDULER_JOB_CLASSES views.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure and "Configuring Oracle Scheduler" for examples of creating job classes.

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.

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

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;
/

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

Using Windows

Windows provide a way to automatically activate different resource plans at different times. Running jobs can then see a change in the resources that are allocated to them when there is a change in resource plan.

The key attributes of a window are its:

• Schedule

  This controls when the window is in effect.

• Duration

  This controls how long the window is open.

• Resource plan

  This names the resource plan that activates when the window opens.

Only one window can be in effect at any given time. Windows belong to the SYS schema.

All window activity is logged in the *_SCHEDULER_WINDOW_LOG views, otherwise known as the window logs. See "Window Logs" for examples of window logging.

This section introduces you to basic window tasks, and discusses the following topics:

• Window Tasks and Their Procedures
• Creating Windows
• Dropping Windows
• Opening Windows
• Closing Windows
• Dropping Windows
• Disabling Windows
• Enabling Windows
• Overlapping Windows

See Also:

Window Tasks and Their Procedures

Table 27-5 illustrates common window tasks and the procedures you use to handle them.

Table 27-5 Window Tasks and Their Procedures

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. There is one difference between these methods, other than the fact that one uses PL/SQL, and the other a graphical user interface. 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. See Oracle Database PL/SQL Packages and Types Reference and "Configuring Oracle Scheduler" for more information.

The following are instructions for creating a window with Enterprise Manager:

1. Access the Database Home page.
2. At the top of the page, click Server to display the Server page.
3. In the Oracle Scheduler section, click Windows.

   The Scheduler Windows page appears. It displays existing windows.

4. Click Create.

   The Create Window page appears.

5. Enter a name for the window.
6. Choose a resource plan from the Resource Plan drop-down list, or create a resource plan.

   You can use the default, INTERNAL_PLAN. To view the contents of an existing resource plan, click View Resource Plan. If you choose to create a new resource plan, click Create Resource Plan and follow those steps.

7. Select a priority, Low or High.
8. Enter optional comments in the Description field.
9. Under the Schedule heading, do one of the following:
   • To create a schedule, select Use a calendar.
   • To use an existing schedule, select Use an existing schedule. A schedule with its information is displayed. If you want a different schedule, click Select Schedule, in which case the Select Schedule page is displayed. Select one of the schedules listed under the Results heading, or enter the schema and a text string for the schedule name, and then click Go. The schedule with the search string in its name is returned. Select the desired schedule and click Select.

     Note:

     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.

     Click OK, and skip the remaining steps in this procedure.

10. If you want to change the time zone, click the flashlight icon next to the Time Zone field and follow the steps.
11. Under the Repeating drop-down list, choose how often you want the window to repeat. If you choose a value other than Do Not Repeat, the page changes so that you can enter the interval and enter a starting time.
12. Under the Start heading, select whether you want the schedule to start Immediately or Later. If you choose Later, enter a date.
13. Under the Duration heading, enter how long the window is to remain open.
14. Click OK to save the window.

Altering Windows

You alter a window using the SET_ATTRIBUTE procedure or Enterprise Manager. With the exception of WINDOW_NAME, all the attributes of a window can be changed when it is altered. The attributes of a window are available in the *_SCHEDULER_WINDOWS views.

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 procedure and "Configuring Oracle Scheduler".

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_WINDOW command 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 FORCE option, 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 the DBMS_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. This means that 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 and Admin for a list of why windows may be disabled.

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.

Overlapping Windows

Although Oracle does not recommend it, windows can overlap. Because only one window can be active at one time, the following rules are used to determine which window will be active when windows overlap:

• If windows of the same priority overlap, the window that is active will stay open. However, if the overlap is with a window of higher priority, the lower priority window will close and the window with the higher priority will open. Jobs currently running that had a schedule naming the low priority window may be stopped depending on the behavior you assigned when you created the job.
• If at the end of a window there are multiple windows defined, the window with the highest priority will open. If all windows have the same priority, the window that has the highest percentage of time remaining will open.
• An open window that is dropped will be automatically closed. At that point, the previous rule applies.

Whenever two windows overlap, an entry is written in the Scheduler log.

Examples of Overlapping Windows

Figure 27-1 illustrates a typical example of how windows, resource plans, and priorities might be determined for a 24 hour schedule. In the following two examples, assume that Window1 has been associated with Resource Plan1, Window2 with Resource Plan2, and so on.

Figure 27-1 Windows and Resource Plans (Example 1)

In Figure 27-1, the following occurs:

• From 12AM to 4AM

  No windows are open, so a default resource plan is in effect.

• From 4AM to 6AM

  Window1 has been assigned a low priority, but it opens because there are no high priority windows. Therefore, Resource Plan 1 is in effect.

• From 6AM to 9AM

  Window3 will open because it has a higher priority than Window1, so Resource Plan 3 is in effect.

• From 9AM to 11AM

  Even though Window1 was closed at 6AM because of a higher priority window opening, at 9AM, this higher priority window is closed and Window1 still has two hours remaining on its original schedule. It will be reopened for these remaining two hours and resource plan will be in effect.

• From 11AM to 2PM

  A default resource plan is in effect because no windows are open.

• From 2PM to 3PM

  Window2 will open so Resource Plan 2 is in effect.

• From 3PM to 8PM

  Window4 is of the same priority as Window2, so it will not interrupt Window2. Therefore, Resource Plan 2 is in effect.

• From 8PM to 10PM

  Window4 will open so Resource Plan 4 is in effect.

• From 10PM to 12AM

  A default resource plan is in effect because no windows are open.

Figure 27-2 illustrates another example of how windows, resource plans, and priorities might be determined for a 24 hour schedule.

Figure 27-2 Windows and Resource Plans (Example 2)

In Figure 27-2, the following occurs:

• From 12AM to 4AM

  A default resource plan is in effect.

• From 4AM to 6AM

  Window1 has been assigned a low priority, but it opens because there are no high priority windows, so Resource Plan 1 is in effect.

• From 6AM to 9AM

  Window3 will open because it has a higher priority than Window1. Note that Window6 does not open because another high priority window is already in effect.

• From 9AM to 11AM

  At 9AM, Window5 or Window1 are the two possibilities. They both have low priorities, so the choice is made based on which has a greater percentage of its duration remaining. Window5 has a larger percentage of time remaining compared to the total duration than Window1. Even if Window1 were to extend to, say, 11:30AM, Window5 would have 2/3 * 100% of its duration remaining, while Window1 would have only 2.5/7 * 100%, which is smaller. Thus, Resource Plan 5 will be in effect.

Using Window Groups

Window groups provide an easy way to schedule jobs that must run during multiple time periods throughout the day, week, and so on. If you create a window group, add windows to it, and then name this window group in a job's schedule_name attribute, the job runs during all the windows in the window group.

Window groups reside in the SYS schema. This section introduces you to basic window group tasks, and discusses the following topics:

• Window Group Tasks and Their Procedures
• Creating Window Groups
• Dropping Window Groups
• Adding a Member to a Window Group
• Dropping a Member from a Window Group
• Enabling a Window Group
• Disabling a Window Group

See Also:

Window Group Tasks and Their Procedures

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

Table 27-6 Window Group Tasks and Their Procedures

See "Scheduler Privileges" for further information regarding privileges.

Creating Window Groups

You create a window group by using the CREATE_WINDOW_GROUP procedure or Enterprise Manager. You can specify the member windows of the group when you create the group, or you can add them later using the ADD_WINDOW_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_WINDOW_GROUP (
   group_name   =>  'downtime',
   window_list  =>  'weeknights, weekends');
END;
/

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

Dropping Window Groups

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

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

BEGIN
DBMS_SCHEDULER.DROP_WINDOW_GROUP('windowgroup1, windowgroup2, windowgroup3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about adding and dropping window groups.

Adding a Member to a Window Group

You add windows to a window group by using the ADD_WINDOW_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 three windows to the window group window_group1:

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ('window_group1',
   'window1, 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.

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

Dropping a Member from a Window Group

You can drop one or more windows from a window group by using the REMOVE_WINDOW_GROUP_MEMBER procedure or Enterprise Manager. 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 three windows:

BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER('window_group1', 'window1, window2,
   window3');
END;
/

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

Enabling a Window Group

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

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

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

Disabling a Window Group

You disable a window group using the DISABLE procedure or Enterprise Manager. This means that jobs with the window group as a schedule will not run even if the member windows open, however, the metadata of the window group is still there, so it can be reenabled. Note that the members of the window group will still open.

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

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

Note that, in this example, the window group will be disabled, but the windows that are members of the window group will not be disabled.

See Oracle Database PL/SQL Packages and T

Using Events

The Scheduler works with two kinds of events:

• Events that the Scheduler raises to notify applications of a change in job state (for example, job complete)
• Events that applications raise to notify the Scheduler to start a job

This section provides details on how to work with both kinds of events, and includes the following topics:

• Using Events Raised by the Scheduler
• Using Events Raised by an Application

See Also:

• "Events" for an overview of Scheduler events
• "Examples of Creating Jobs and Schedules Based on Events"
• "Using Chains" for information on how to use events with chains to achieve precise control over process flow

Using Events Raised by the Scheduler

You can set up a job so that the Scheduler raises an event when the job changes state. You do so by setting the raise_events job attribute. Because you cannot set this attribute with the CREATE_JOB procedure, you must first create the job and then alter the job with the SET_ATTRIBUTE procedure.

By default, until you alter a job with SET_ATTRIBUTE, a job does not raise any state change events.

Table 27-7 summarizes the one administration task involving events raised by the Scheduler.

Table 27-7 Event Tasks and Their Procedures for Events Raised by the Scheduler

After you enable job state change events for a job, the Scheduler raises these events by enqueuing messages onto the Scheduler event queue SYS.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. See Oracle Database PL/SQL Packages and Types Reference for more information.

Altering a Job to Raise Events

To enable job state change events 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;
/

See Also:

Consuming Scheduler-Raised Events with your Application

To consume Scheduler 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 is NULL, 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.

Scheduler Event Queue

The Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE is of type scheduler$_event_info. The following are details on this type.

create or replace type sys.scheduler$_event_info as object
(
  event_type         VARCHAR2(4000),
  object_owner       VARCHAR2(4000),
  object_name        VARCHAR2(4000),
  event_timestamp    TIMESTAMP WITH TIME ZONE,
  error_code         NUMBER,
  error_msg          VARCHAR2(4000),
  event_status       NUMBER,
  log_id             NUMBER,
  run_count          NUMBER,
  failure_count      NUMBER,
  retry_count        NUMBER,
  spare1             NUMBER,
  spare2             NUMBER,
  spare3             VARCHAR2(4000),
  spare4             VARCHAR2(4000),
  spare5             TIMESTAMP WITH TIME ZONE,
  spare6             TIMESTAMP WITH TIME ZONE,
  spare7             RAW(2000),
  spare8             RAW(2000),
);

Using Events Raised by an Application

Your application can raise an event to notify the Scheduler to start a job. A job started in this way is referred to as an event-based job. The job can optionally retrieve the message content of the event.

To create an event-based job, you must set these two additional attributes:

• queue_spec

  A queue specification that includes the name of the queue where your application enqueues messages to raise job start events, or in the case of a secure queue, the queue name followed by a comma and the agent name.

• event_condition

  A conditional expression based on message properties that must evaluate to TRUE for the message to start the job. The expression must have the syntax of an Oracle Streams Advanced Queuing rule. Accordingly, you can include user data properties in the expression, provided that the message payload is an object type, and that you prefix object attributes in the expression with tab.user_data.

  For more information on rules, see the DBMS_AQADM.ADD_SUBSCRIBER procedure in Oracle Database PL/SQL Packages and Types Reference.

  The following example sets event_condition to select only card-swipe events that occur after midnight and before 9:00 a.m. Assume that the message payload is an object with two attributes called event_type and event_timestamp.

  event_condition = 'tab.user_data.event_type = ''CARD_SWIPE'' and 
  extract hour from tab.user_data.event_timestamp < 9'
  

You can specify queue_spec and event_condition as inline job attributes, or you can create an event schedule with these attributes and point to this schedule from the job.

Note:

Table 27-8 describes common administration tasks involving events raised by an application (and consumed by the Scheduler) and the procedures associated with them.

Table 27-8 Event Tasks and Their Procedures for Events Raised by an Application

See Also:

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.

Specifying Event Information as Job Attributes

To specify event information as job attributes, you use an alternate syntax of CREATE_JOB that includes the queue_spec and event_condition attributes.

The following example creates a job that starts whenever someone swipes a badge to enter a data center:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_job',
   program_name        =>  'my_program',
   event_condition     =>  'tab.user_data.event_type = ''CARD_SWIPE''',
   queue_spec          =>  'entry_events_q, entry_agent1',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
END;
/

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

Specifying Event Information in an Event Schedule

To specify event information with an event schedule, you set the job's schedule_name attribute to the name of an event schedule, as shown in the following example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_job',
   program_name        =>  'my_program',
   schedule_name       =>  'entry_events_schedule',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
END;
/

See "Creating an Event Schedule" for more information.

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 and event_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 = ''BAD_BADGE''', 'entry_events_q, entry_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_SCHEDULE procedure, or use Enterprise Manager. The following is an example of creating an event schedule:

BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'entry_events_schedule',
   start_date        =>  SYSTIMESTAMP,
   event_condition   =>  'tab.user_data.event_type = ''CARD_SWIPE''', 
   queue_spec        =>  'entry_events_q, entry_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 ('entry_events_schedule', 'event_spec',
   'tab.user_data.event_type = ''BAD_BADGE''', 'entry_events_q, entry_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
  -- do other work
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 ;
/

Using Chains

A chain is a named series of programs that are linked together for a combined objective. To create and use a chain, you complete these steps in order:

Other topics discussed in this section include:

• Chain Tasks and Their Procedures
• Dropping Chains
• Running Chains
• Dropping Rules from a Chain
• Disabling Chains
• Dropping Chain Steps
• Altering Chain Steps
• Handling Stalled Chains

See Also:

• "Chains" for an overview of chains
• "Examples of Creating Chains"

Chain Tasks and Their Procedures

Table 27-9 illustrates common tasks involving chains and the procedures associated with them.

Table 27-9 Chain Tasks and Their Procedures

Creating Chains

You create a chain by using the CREATE_CHAIN procedure. After creating the chain object with CREATE_CHAIN, you define chain steps and chain rules separately.

The rule_set_name and evaluation_interval arguments are normally left NULL. evaluation_interval can define the times that chain rules get evaluated, other than when the job starts or a step completes. rule_set_name is for advanced users only.

See Oracle Database PL/SQL Packages and Types Reference for more information on CREATE_CHAIN.

The following is an example of creating a chain:

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

Defining Chain Steps

After creating a chain object, you define one or more chain steps. Each step can point to one of the following:

• A program
• Another chain (a nested chain)
• An event

You define a step that points to a program or nested chain by using the DEFINE_CHAIN_STEP procedure. An example is the following, which 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;
/

To define a step that waits for an event to occur, you use the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule or can include an inline queue specification and event condition. 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;
/

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

Adding Rules to a Chain

Chain rules define when steps run, and define dependencies between steps. Each rule has a condition and an action. If the condition evaluates to TRUE, the 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.

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.

All rules added to a chain work together to define the overall behavior of the chain. When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. (You can cause rules to be evaluated at regular intervals also. See "Creating Chains" for details.)

You add a rule to a chain with the DEFINE_CHAIN_RULE procedure. You call this procedure once for each rule that you want to add to the chain.

Starting the Chain

At least one rule must have a condition that always evaluates to TRUE so that the chain can start when the job starts. The easiest way to accomplish this is to just 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 or 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 job enters the CHAIN_STALLED state. See "Handling Stalled Chains" for more information.

Example

The following example defines a rule that starts the chain at step 1 and a rule that starts step 2 when step 1 completes. rule_name and comments are optional and default to NULL.

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;
/

See Also:

• Oracle Database PL/SQL Packages and Types Reference for information on the DEFINE_CHAIN_RULE procedure and on Scheduler chain condition syntax.
• "Examples of Creating Chains"

Enabling Chains

You enable a chain with the ENABLE procedure. A chain must be enabled before it can be run by a job. Enabling an already enabled chain does not return an error.

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

Note:

• The program that one of the chain steps points to is dropped
• The nested chain that one of the chain steps points to is dropped
• The event schedule that one of the chain event steps points to is dropped

Creating Jobs for Chains

To run a chain, you must either use the RUN_CHAIN procedure or create a job of type 'CHAIN'. The job action must refer to the 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. The job subname is included 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'.

See Also:

• Oracle Database PL/SQL Packages and Types Reference for more information on the CREATE_JOB procedure.
• "Running Chains" for another way to run a chain without creating a job ahead of time.

Dropping Chains

You drop a chain, including its steps and rules, by using the DROP_CHAIN procedure. An example of dropping a chain is the following, which drops 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.

Running Chains

You can use the RUN_CHAIN procedure to run a chain immediately, without having to create a job ahead of time 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. An example is the following, which immediately runs the chain my_chain1:

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

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

Dropping Rules from a Chain

You drop a rule from a chain by using the DROP_CHAIN_RULE procedure. An example is the following, which 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.

Disabling Chains

You disable a chain by using the DISABLE procedure. An example is the following, which 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.

Note:

• The program that one of the chain steps points to is dropped
• The nested chain that one of the chain steps points to is dropped
• The event schedule that one of the chain event steps points to is dropped

Dropping Chain Steps

You drop a step from a chain by using the DROP_CHAIN_STEP procedure. An example is the following, which 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.

Altering Chain Steps

You alter the SKIP, PAUSE, or RESTART_ON_RECOVERY attributes of a chain step by using the ALTER_CHAIN procedure. An example is the following, which causes my_step3 to be skipped:

BEGIN
DBMS_SCHEDULE.ALTER_CHAIN (
   chain_name  =>  'my_chain1',
   step_name   =>  'my_step3',
   attribute   =>  'SKIP',
   value       =>  TRUE);
END;
/

The ALTER_CHAIN procedure affects only future runs of the specified steps.

You alter the steps in a running chain by using the ALTER_RUNNING_CHAIN procedure. An example is the following, which causes step my_step1 to pause after it has completed-that is, its state is changed to PAUSED and its completed attribute remains FALSE:

BEGIN
DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (
   job_name     =>   'my_job1',
   step_name    =>   'my_step1',
   attribute    =>   'PAUSE',
   value        =>    TRUE);
END;
/

The ALTER_RUNNING_CHAIN procedure affects only the running instance of the chain.

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

Handling Stalled Chains

A chain can become stalled when no steps are running, no steps are scheduled to run, no event steps are waiting for an event, and the evaluation_interval for the chain is NULL. 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.

Allocating Resources Among Jobs

It is not practical to manage resource allocation at an individual job level, therefore, the Scheduler uses the concept of job classes to manage resource allocation among jobs. In addition to job classes, the Scheduler uses the Resource Manager to manage resource allocation among jobs.

Allocating Resources Among Jobs Using Resource Manager

The Database Resource Manager controls how resources are allocated among database sessions. It not only controls asynchronous sessions like jobs but also synchronous sessions like user sessions. It groups all "units of work" in the database into resource consumer groups and uses a resource plan to specify how the resources are allocated among the various groups. See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information about what resources are controlled by the Resource Manager.

For jobs, resource allocation is specified by associating a job class with a consumer group, or by associating a job class with a database service name and mapping that database service to a consumer group. The consumer group that a job class maps to can be specified when creating a job class. If no resource consumer group or database service name is specified when a job class is created, the job class maps to the default consumer group. If both the resource_consumer_group and service attributes of a job class are set, and the designated service maps to a resource consumer group, the resource consumer group named in the resource_consumer_group attribute takes precedence.

The Scheduler tries to limit the number of jobs that are running simultaneously so that at least some jobs can complete, rather than running a lot of jobs concurrently but without enough resources for any of them to complete.

The Scheduler and the Resource Manager are tightly integrated. The job coordinator obtains database resource availability from the Resource Manager. Based on that information, the coordinator determines how many jobs to start. It will only start jobs from those job classes that will have enough resources to run. The coordinator will keep starting jobs in a particular job class that maps to a consumer group until the Resource Manager determines that the maximum resource allocated for that consumer group has been reached. This means that it is possible that there will be jobs in the job table that are ready to run but will not be picked up by the job coordinator because there are no resources to run them. Therefore, there is no guarantee that a job will run at the exact time that it was scheduled. The coordinator picks up jobs from the job table on the basis of which consumer groups still have resources available.

Even when jobs are running, the Resource Manager will continue to manage the resources that are assigned to each running job based on the specified resource plan. Keep in mind that the Resource Manager can only manage database processes. The active management of resources does not apply to external jobs.

In a database, only one resource plan can be in effect at one time. It is possible to manually switch the resource plan that is active on a system using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure. In special scenarios, you might want to run a specific resource plan and disable resource plan switches caused by windows opening. To do this, you can use the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure with allow_scheduler_plan_switches set to FALSE. Also remember that a Scheduler window can have a resource plan attribute. The designated resource plan remains active while the window is open.

Example of Resource Allocation for Jobs

The following example can help to understand how resources are allocated for jobs. Assume that the active resource plan is called "Night Plan" and that there are three job classes: JC1, which maps to consumer group DW; JC2, which maps to consumer group OLTP; and JC3, which maps to the default consumer group. Figure 27-3 offers a simple graphical illustration of this scenario.

Figure 27-3 Sample Resource Plan

This resource plan clearly gives priority to jobs that are part of job class JC1. Consumer group DW gets 60% of the resources, thus jobs that belong to job class JC1 will get 60% of the resources. Consumer group OLTP has 30% of the resources, which implies that jobs in job class JC2 will get 30% of the resources. The consumer group Other specifies that all other consumer groups will be getting 10% of the resources. This means that all jobs that belong in job class JC3 will share 10% of the resources and can get a maximum of 10% of the resources.

Note that resources that remain unused by one consumer group are available from use by the other consumer groups. So if the jobs in job class JC1 do not fully use the allocated 60%, the unused portion is available for use by jobs in classes JC2 and JC3. Note also that the Resource Manager does not begin to restrict resource usage at all until CPU usage reaches 100%. See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information.

28 Administering Oracle Scheduler

In this chapter:

• Configuring Oracle Scheduler
• Monitoring and Managing the Scheduler
• Enabling and Disabling Remote External Jobs
• Import/Export and the Scheduler
• Troubleshooting the Scheduler
• Examples of Using the Scheduler
• Scheduler Reference

Note:

To administer the Scheduler with Enterprise Manager:

1. Access the Database Home page.

   See Oracle Database 2 Day DBA for instructions.

2. At the top of the page, click to display the Server property page.
3. Locate the Oracle Scheduler section on the page, and then click the desired link.

Configuring Oracle Scheduler

The following tasks are necessary when configuring Oracle Scheduler (the Scheduler):

• Task 1: Setting Scheduler Privileges
• Task 2: Configuring the Scheduler Environment

Task 1: Setting Scheduler Privileges

You should have the SCHEDULER_ADMIN role to administer the Scheduler. Typically, database administrators will already have this role with the ADMIN option as part of the DBA (or equivalent) role. You can grant this role to another administrator by issuing the following statement:

GRANT SCHEDULER_ADMIN TO username;

Because the SCHEDULER_ADMIN role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Object and system privileges are granted using regular SQL grant syntax. An example is if the database administrator issues the following statement:

GRANT CREATE JOB TO scott;

After this statement is executed, scott can create jobs, schedules, or programs in his schema. Another example is if the database administrator issues the following statement:

GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, adam can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

Setting Chain Privileges

Scheduler chains use underlying Oracle Streams Rules Engine objects along with their associated privileges. To create a chain in his own schema, a user must have the CREATE JOB privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in his own schema. These can be granted by issuing the following statement:

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 'username')
END;
/

To create a chain in a different schema, a user must have the CREATE ANY JOB privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than his own. These can be granted by issuing the following statement:

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_ANY_RULE, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_ANY_RULE_SET, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT, 'username')
END;
/

Altering or dropping chains in schemas other than the user's schema will require corresponding system Rules Engine privileges for rules, rule sets, and evaluation contexts. See the usage notes for DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE for more information on Streams Rules Engine privileges.

See Also:

• "Scheduler Privileges"
• "Chain Tasks and Their Procedures" for more information regarding chain privileges.

Task 2: Configuring the Scheduler Environment

This section discusses the following tasks:

• Task 2A: Creating Job Classes
• Task 2B: Creating Windows
• Task 2C: Creating Resource Plans
• Task 2D: Creating Window Groups
• Task 2E: Setting Scheduler Attributes

Task 2A: Creating Job Classes

To create job classes, use the CREATE_JOB_CLASS procedure. The following statement illustrates an example of creating a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              => 'my_jobclass1',
   resource_consumer_group     => 'my_res_group1', 
   comments                    => 'This is my first job class.');
END;
/

This statement creates a job class called my_jobclass1 with attributes such as a resource consumer group of my_res_group1. To verify the job class contents, issue the following statement:

SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;

JOB_CLASS_NAME        RESOURCE_CONSU   SERVICE   LOGGING_LEV  LOG_HISTORY    COMMENTS
-----------------     --------------   -------   -----------  -----------    --------
DEFAULT_JOB_CLASS                                       RUNS                 The default
AUTO_TASKS_JOB_CLASS  AUTO_TASK_CON                     RUNS                 System maintenance
FINANCE_JOBS          FINANCE_GROUP                     RUNS    
MY_JOBCLASS1          MY_RES_GROUP1                     RUNS                 My first job class
MY_CLASS1                              my_service1      RUNS                 My second job class

5 rows selected.

Note that job classes are created in the SYS schema.

See Also:

Task 2B: Creating Windows

To create windows, use the CREATE_WINDOW procedure. The following statement illustrates an example of creating a window:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name     =>  'my_window1',
   resource_plan   =>  'my_resourceplan1',
   start_date      =>  '15-APR-03 01.00.00 AM Europe/Lisbon',
   repeat_interval =>  'FREQ=DAILY',
   end_date        =>  '15-SEP-04 01.00.00 AM Europe/Lisbon',
   duration        =>  interval '50' minute,
   window_priority =>  'HIGH',
   comments        =>  'This is my first window.');
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
-----------    -------------     -------------    ---------------
MY_WINDOW1     MY_RESOURCEPLAN1  +000 00:50:00    FREQ=DAILY

See Also:

Task 2C: Creating Resource Plans

To create resource plans, use the CREATE_SIMPLE_PLAN procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement.

The following statement illustrates an example of using this procedure to create a resource plan called my_simple_plan1:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
   simple_plan       => 'my_simple_plan1',
   consumer_group1   => 'my_group1',
   group1_cpu        => 80,
   consumer_group2   => 'my_group2',
   group2_cpu        => 20);
END;
/

This statement creates a resource plan called my_simple_plan1. To verify the resource plan contents, query the view DBA_RSRC_PLANS. An example is the following statement:

SELECT PLAN, STATUS FROM DBA_RSRC_PLANS;

PLAN                           STATUS
------------------------------ --------------------------
SYSTEM_PLAN                    ACTIVE
INTERNAL_QUIESCE               ACTIVE
INTERNAL_PLAN                  ACTIVE
MY_SIMPLE_PLAN1                ACTIVE

See Also:

Task 2D: Creating Window Groups

To create window groups, use the CREATE_WINDOW_GROUP and ADD_WINDOW_GROUP_MEMBER procedures. The following statements illustrate an example of using these procedures:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
   group_name        =>  'my_window_group1',
   comments          =>  'This is my first window group.');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name        =>  'my_window_group1',
   window_list       =>  'my_window1, my_window2');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name        =>  'my_window_group1',
   window_list       =>  'my_window3');
END;
/

These statements assume that you have already created my_window2 and my_window3. You can do this with the CREATE_WINDOW procedure.

These statements create a window group called my_window_group1 and then add my_window1, my_window2, and my_window3 to it. To verify the window group contents, issue the following statements:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME   ENABLED  NUMBER_OF_WINDOWS   COMMENTS
-----------------   -------  -----------------   --------------------
MY_WINDOW_GROUP1    TRUE                     3   This is my first window group.

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ---------------
MY_WINDOW_GROUP1               MY_WINDOW1
MY_WINDOW_GROUP1               MY_WINDOW2
MY_WINDOW_GROUP1               MY_WINDOW3

See Also:

Task 2E: Setting Scheduler Attributes

There are several Scheduler attributes that control the behavior of the Scheduler. They are default_timezone, log_history, max_job_slave_processes, and event_expiry_time. The values of these attributes can be set by using the SET_SCHEDULER_ATTRIBUTE procedure. Setting these attributes requires the MANAGE SCHEDULER privilege. Attributes that can be set are:

• default_timezone

  Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. They normally retrieve the time zone from start_date, but if no start_date is provided (which is not uncommon), they retrieve the time zone from the default_timezone Scheduler attribute.

  Scheduler derives the value of default_timezone from the operating system environment. If Scheduler can find no compatible value from the operating system, it sets default_timezone to NULL.

  It is crucial that you verify that default_timezone is set properly, and if not, that you set it. To verify it, run this query:

  SQL> select dbms_scheduler.stime from dual;
   
  STIME
  ---------------------------------------------------------------------------
  14-OCT-04 02.56.03.206273000 PM US/PACIFIC
  

  To ensure that daylight savings adjustments are followed, it is strongly recommended that you set default_timezone to a region name instead of an absolute time zone offset. For example, if your database resides in Miami, Florida, USA, issue the following statement:

  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');
  

  To see a list of valid region names, run this query:

  SELECT DISTINCT TZNAME FROM V$TIMEZONE_NAMES;
  

  If you do not properly set default_timezone, the default time zone for repeating jobs and windows will be the absolute offset retrieved from SYSTIMESTAMP (the time zone of the operating system environment of the database), which means that repeating jobs and windows that do not have their start_date set will not follow daylight savings adjustments.

• log_history

  This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler automatically purges all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days.

  You can change the default by using the SET_SCHEDULER_ATTRIBUTE procedure. For example, to change it to 90 days, issue the following statement:

  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');
  

  The range of valid values is 1 through 999.

• max_job_slave_processes

  This enables you to set a maximum number of slave processes for a particular system configuration and load. Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute. The default value is NULL, and the valid range is 1-999.

  Although the number set by max_job_slave_processes is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.

• event_expiry_time

  This enables you to set the time in seconds before an event generated by the Scheduler expires (in other words, is automatically purged from the queue).

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

Monitoring and Managing the Scheduler

The following sections discuss how to monitor and manage the Scheduler:

• Viewing the Currently Active Window and Resource Plan
• Finding Information About Currently Running Jobs
• Monitoring and Managing Window and Job Logs
• Changing Job Priorities
• Monitoring Running Chains
• Managing Scheduler Security

Viewing the Currently Active Window and Resource Plan

You can view the currently active window and the plan associated with it by issuing the following statement:

SELECT WINDOW_NAME, RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS
WHERE ACTIVE='TRUE';

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ --------------------------
MY_WINDOW10                    MY_RESOURCEPLAN1

If there is no window active, you can view the active resource plan by issuing the following statement:

SELECT * FROM V$RSRC_PLAN;

Finding Information About Currently Running Jobs

You can check a job's state by issuing the following statement:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'MY_EMP_JOB1';

JOB_NAME                       STATE
------------------------------ ---------
MY_EMP_JOB1                    DISABLED

In this case, you could enable the job using the ENABLE procedure. Table 28-1 shows the valid values for job state.

Table 28-1 Job States

You can check the progress of currently running jobs by issuing the following statement:

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

Note that, for the column CPU_USED to show valid data, the initialization parameter RESOURCE_LIMIT must be set to true.

You can find out information about a job that is part of a running chain by issuing the following statement:

SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='MY_JOB1';

You can check whether the job coordinator is running by searching for a process of the form cjqNNN.

See Also:

Monitoring and Managing Window and Job Logs

Logs have a new entry for each event that occurs so that you can track historical information. This is different from a queue, where you only want to track the latest status for an item. There are logs for jobs, job runs, and windows.

Job activity is logged in the *_SCHEDULER_JOB_LOG views. Altering a job is logged with an operation of UPDATE. Dropping a job is logged in these views with an operation of DROP.

See Also:

Job Logs

To see the contents of the job log, query the DBA_SCHEDULER_JOB_LOG view. An example is the following statement, which shows what happened for past job runs:

SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;

JOB_NAME        OPERATION     OWNER
--------        ---------     -----
MY_JOB13        CREATE        SYS
MY_JOB14        CREATE        OE
MY_NEW_JOB3     ENABLE        SYS
MY_EMP_JOB1     UPDATE        SYS
MY_JOB1         CREATE        SCOTT
MY_EMP_JOB1     UPDATE        SYS
MY_EMP_JOB      CREATE        SYS
MY_JOB14        RUN           OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        BROKEN        OE
MY_JOB14        DROP          OE

When logging_level for a job is set to LOGGING_FULL, the additional_info column of the job log contains the before and after values of the modified attribute on update operations, and contains the values of all attributes on drop operations. This enables you to trace backwards from the current job state to the state of the job on previous job runs.

Job Run Details

To further analyze each job run-why it failed, what the actual start time was, how long the job ran, and so on-query the DBA_SCHEDULER_JOB_RUN_DETAILS view. As an example, the following statement illustrates the status for my_job14:

select log_id, job_name, status, 
to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date 
from dba_scheduler_job_run_details
where job_name = 'MY_JOB14';

    LOG_ID JOB_NAME               STATUS       LOG_DATE
---------- ---------------------- ------------ -----------------
        69 MY_JOB14               SUCCEEDED    02-JUN-2005 03:14
       124 MY_JOB14               SUCCEEDED    03-JUN-2005 03:15
       133 MY_JOB14               FAILURE      04-JUN-2005 03:00
       146 MY_JOB14               FAILURE      05-JUN-2005 03:01

For every row in SCHEDULER_JOB_LOG that is of operation RUN, RETRY_RUN, or RECOVERY_RUN, there will be a corresponding row in the *_JOB_RUN_DETAILS view with the same LOG_ID. LOG_DATE contains the timestamp of the entry, so sorting by LOG_DATE should give you a chronological picture of the life of a job.

Controlling Job Logging

You can control the amount of logging that the Scheduler performs on jobs at either a class or job level. Normally, you will want to control jobs at a class level as this offers a full audit trail. To do this, use the logging_level attribute in the CREATE_JOB_CLASS procedure.

For each new class, the creator of the class must specify what the logging level is for all jobs in that class. The three possible options are:

• DBMS_SCHEDULER.LOGGING_OFF

  No logging will be performed for any jobs in this class.

• DBMS_SCHEDULER.LOGGING_RUNS

  The Scheduler will write detailed information to the job log for all runs of each job in this class.

• DBMS_SCHEDULER.LOGGING_FULL

  In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

By default, only job runs are recorded. 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. You might, however, prefer to have a complete audit trail of everything that happened to the jobs in a specific class, in which case you need to turn on full logging for that class.

The second way of controlling the logging level is on an individual job basis. You should keep in mind, however, that the log in many cases is used as an audit trail, thus if you want a certain level of logging, the individual job creator must not be able to turn logging off. The class-specific level is, therefore, the minimum level at which job information will be logged. A job creator can only turn on more logging for an individual job, not less.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and the job-specific logging is turned off, the Scheduler will still log the runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, all operations on this individual job will be 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 called mytestjob, issue the following statement:

DBMS_SCHEDULER.SET_ATTRIBUTE (
   'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

See Also:

Window Logs

A window log has an entry for each time you do the following:

• Create a window
• Drop a window
• Open a window
• Close a window
• Overlap windows
• Disable a window
• Enable a window

There are no logging levels for window logging, but every window operation will automatically be logged by the Scheduler.

To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:

SELECT LOG_ID, TO_CHAR(LOG_DATE, 'MM/DD/YYYY'), WINDOW_NAME, OPERATION
  FROM DBA_SCHEDULER_WINDOW_LOG;

    LOG_ID TO_CHAR(LO WINDOW_NAME       OPERATION
---------- ---------- ----------------- ------------------------------
         1 10/01/2004 WEEKNIGHT_WINDOW  CREATE
         2 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         3 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         4 10/01/2004 WEEKEND_WINDOW    CREATE
         5 10/01/2004 WEEKEND_WINDOW    UPDATE
         6 10/01/2004 WEEKEND_WINDOW    UPDATE
        22 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        25 10/06/2004 WEEKNIGHT_WINDOW  CLOSE
        26 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        29 10/06/2004 WEEKNIGHT_WINDOW  CLOSE

The DBA_SCHEDULER_WINDOWS_DETAILS view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:

SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION
  FROM DBA_SCHEDULER_WINDOW_DETAILS;

    LOG_ID WINDOW_NAME      ACTUAL_START_DATE                    ACTUAL_DURATI
---------- ---------------- ------------------------------------ -------------
        25 WEEKNIGHT_WINDOW 06-OCT-04 03.12.48.832438 PM PST8PDT +000 01:02:32
        29 WEEKNIGHT_WINDOW 06-OCT-04 06.19.37.025704 PM PST8PDT +000 03:02:00

Notice that log IDs correspond in both of these views, and that in this case the rows in the DBA_SCHEDULER_WINDOWS_DETAILS view correspond to the CLOSE operations in the DBA_SCHEDULER_WINDOW_LOG view.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE procedure to specify how much history (in days) to keep. Once per day, the Scheduler automatically purges all log entries that are older than the specified history period from both the job log and the window log. The default history period is 30 days. For example, to change the history period to 90 days, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

Some job classes are more important than others. Because of this, you can override this global history setting by using a class-specific setting. For example, suppose that there are three job classes (class1, class2, and class3), and that you want to keep 10 days of history for the window log, class1, and class3, but 30 days for class2. To achieve this, issue the following statements:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','10');
DBMS_SCHEDULER.SET_ATTRIBUTE('class2','log_history','30');

You can also set the class-specific history when creating the job class.

Note that log entries pertaining to steps of a chain run are not purged until the entries for the main chain job are purged.

Purging Logs Manually

The PURGE_LOG procedure enables you to manually purge logs. As an example, the following statement purges all entries from both the job and window logs:

DBMS_SCHEDULER.PURGE_LOG();

Another example is the following, which purges all entries from the jog log that are older than three days. The window log is not affected by this statement.

DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');

The following statement purges all window log entries older than 10 days and all job log entries older than 10 days that relate to job1 and to the jobs in class2:

DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');

Changing Job Priorities

You can change job priorities by using the SET_ATTRIBUTE procedure. Job priorities must be in the range 1-5 with 1 being the highest priority. 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

See Also:

Monitoring Running Chains

You can check the state of a running chain by querying the *_SCHEDULER_RUNNING_CHAINS views. The results contain a row describing the current state of every step in every running instance of a chain. For example, the following statement displays the state of all steps in the running job MY_CHAIN_JOB. It also shows the state of all steps of any nested chain jobs that are running or have completed.

SELECT * FROM USER_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME = 'MY_CHAIN_JOB';

See "Using Chains" for more information regarding chains.

Managing Scheduler Security

You should grant the CREATE JOB system privilege to regular users who need to be able to use the Scheduler to schedule and run jobs. You should grant MANAGE SCHEDULER to any database administrator who needs to be able to manage system resources. Granting any other Scheduler system privilege or role should not be done without great caution. In particular, the CREATE ANY JOB system privilege and the SCHEDULER_ADMIN role, which includes it, are very powerful because they allow execution of code as any user. They should only be granted to very powerful roles or users.

A particularly important issue from a security point of view is handling external jobs. Only users that need to run jobs outside of the database should be allowed to do so. You must grant the CREATE EXTERNAL JOB system privilege to those users. See "Creating Remote External Jobs" for further information. Security for the Scheduler has no other special requirements. See Oracle Database Security Guide for details regarding security.

Note:

Enabling and Disabling Remote External Jobs

Oracle Scheduler (the Scheduler) can schedule and run external jobs on a remote host. The remote host does not need an Oracle database installed; however, a Scheduler agent must be installed on the remote host so that the scheduling database can start remote external jobs on that host and receive job output and error information. The agent must register with every database that is to be permitted to start remote external jobs on the agent's host computer. An initial setup is also required for each database that is to run remote external jobs. This setup enables secure communications between the database and remote Scheduler agents.

Enabling remote external jobs involves the following steps:

1. Setting Up the Database
2. Installing, Configuring, and Starting the Scheduler Agent

This section also contains the following topics:

• Stopping the Scheduler Agent
• Disabling Remote External Jobs

See Also:

Setting Up the Database

Before a database can execute jobs using a remote Scheduler agent, the agent must be registered with the database. To make the registration of remote Scheduler agents secure, an agent registration password must be set on the database. You can limit the number of Scheduler agents that can register, and you can set the password to expire after a specified duration.

Complete the following steps for each database that is to run remote external jobs.

To set up the database to run remote external jobs:

1. Using SQL*Plus, connect to the database as the SYS user.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Enter the following command to verify that the XML DB option is installed:

   SQL> DESC RESOURCE_VIEW
   

   If XML DB is not installed, this command returns an "object does not exist" error.

   Note:

   If XML DB is not installed, you must install it before continuing.
3. Enable HTTP connections to the database by submitting the following PL/SQL block:

   BEGIN
     DBMS_XDB.SETHTTPPORT(port);
   END;
   

   where port is the TCP port number on which you want the database to listen for HTTP connections.

   port must be an integer between 1 and 65536, and for UNIX and Linux must be greater than 1023. Choose a port number that is not already in use.

4. Run the script prvtrsch.plb with following command:

   SQL> @?/rdbms/admin/prvtrsch.plb
   

5. Set a registration password for the Scheduler agents using the SET_AGENT_REGISTRATION_PASS procedure.

   The following example sets the agent registration password to mypassword.

   BEGIN
     DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
   END;
   

   Note:

   The MANAGE SCHEDULER privilege is required to set an agent registration password. See Oracle Database PL/SQL Packages and Types Reference for more information on the SET_AGENT_REGISTRATION_PASS procedure.

Installing, Configuring, and Starting the Scheduler Agent

Before you can run remote external jobs on a particular remote host, you must install, configure, and start the Scheduler agent on that host. The Scheduler agent is included with the installation package for Oracle Database Gateways, which is included in the Database Media Pack. It is also available online at:

http://www.oracle.com/technology/software/products/database

The Scheduler agent must be installed in its own Oracle home.

To install, configure, and start the Scheduler agent on a remote host:

1. Log in to the remote host.
   • For Windows, log in as an administrator.
   • For UNIX and Linux, log in as the user under which you want the Scheduler agent to run. This user requires no special privileges.
2. Run the Oracle Universal Installer (OUI) from the installation media for Oracle Database Gateway.
   • For Windows, run setup.exe.
   • For UNIX and Linux, use the following command:

     /directory_path/runInstaller
     

   where directory_path is the path to the Oracle Database Gateway installation media.

3. On the OUI Welcome page, click Next.
4. On the product selection page, select Oracle Scheduler Agent, and then click Next.
5. On the Specify Home Details page, enter a name and path for a new Oracle home for the agent, and then click Next.
6. On the Oracle Scheduler Agent page, complete these steps:
   1. In the Scheduler Agent Host Name field, enter the host name of the computer on which the Scheduler agent is to run, or accept the default host name.
   2. In the Scheduler Agent Port Number field, enter the TCP port number on which the Scheduler agent is to listen for connections, and then click Next.

      Choose an integer between 1 and 65535. On UNIX and Linux, the number must be greater than 1023. Ensure that the port number is not already in use.

7. On the Summary page, click Install.
8. (UNIX and Linux only) When the Oracle Universal Installer prompts you to run the script root.sh, enter the following command as the root user:

   script_path/root.sh
   

   The script is located in the directory that you chose for agent installation.

9. Exit OUI when installation is complete.
10. Use a text editor to review the agent configuration parameter file schagent.conf, which is located in the Scheduler agent home directory, and verify the port number in the PORT= directive.

    You will need this port number when creating remote external jobs. Change any other agent configuration parameters as required.

11. Register the Scheduler agent with a database that is to run remote external jobs on the agent's host computer. Use the following command:

    AGENT_HOME/bin/schagent -registerdatabase db_host db_http_port
    

    where:

    • db_host is the host name or IP address of the host on which the database resides.
    • db_http_port is the port number that the database listens on for HTTP connections. You set this parameter previously in "Setting Up the Database". You can check the port number by submitting the following SQL statement to the database:

      SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
      

      A port number of 0 means that HTTP connections are disabled.

    The agent prompts you to enter the agent registration password that you set in "Setting Up the Database".

12. Repeat the previous step for each database that is to run remote external jobs on the agent's host.
13. (UNIX and Linux only) Start the Scheduler agent with the following command:

    AGENT_HOME/bin/schagent -start
    

    Note:

    On Windows, a Scheduler agent service is automatically created and started during installation. The name of the service ends with OracleSchedulerExecutionAgent. Do not confuse this service with the OracleJobScheduler service, which runs on a Windows computer on which an Oracle database is installed, and manages the running of local external jobs without credentials.

Stopping the Scheduler Agent

Stopping the Scheduler agent prevents the host on which it resides from running remote external jobs.

To stop the Scheduler agent:

• Do one of the following:
  • On UNIX and Linux, run the following command:

    AGENT_HOME/bin/schagent -stop
    

  • On Windows, stop the service whose name ends with OracleSchedulerExecutionAgent.

Disabling Remote External Jobs

You can disable the capability of a database to run remote external jobs by dropping the REMOTE_SCHEDULER_AGENT user.

To disable remote external jobs:

• Submit the following SQL statement:

  DROP USER REMOTE_SCHEDULER_AGENT CASCADE;
  

Registration of new scheduler agents and execution of remote external jobs is disabled until you run prvtrsch.plb again.

Import/Export and the Scheduler

You must use the Data Pump utilities (impdp and expdp) to export Scheduler objects. You cannot use the earlier import/export utilities (IMP and EXP) with the Scheduler. Also, Scheduler objects cannot be exported while the database is in read-only mode.

An export generates the DDL that was used to create the Scheduler objects. All attributes are exported. When an import is done, all the database objects are recreated in the new database. All schedules are stored with their time zones, which are maintained in the new database. For example, schedule "Monday at 1 PM PST in a database in San Francisco" would be the same if it was exported and imported to a database in Germany.

Although Scheduler credentials are exported, for security reasons, the passwords in these credentials are not exported. After you import Scheduler credentials, you must reset the passwords using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package.

See Also:

Troubleshooting the Scheduler

This section contains the following troubleshooting topics:

• Understanding Why a Job Fails to Run
• Job Recovery After a Failure
• Understanding Why a Program Becomes Disabled
• Understanding Why a Window Fails to Take Effect

Understanding Why a Job Fails to Run

A job may fail to run for several reasons. Before troubleshooting a job that you suspect did not run, check that the job is not running by issuing the following statement:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

Typical output will resemble the following:

JOB_NAME                       STATE
------------------------------ ---------
MY_EMP_JOB                     DISABLED
MY_EMP_JOB1                    FAILED
MY_NEW_JOB1                    DISABLED
MY_NEW_JOB2                    BROKEN
MY_NEW_JOB3                    COMPLETED

There are four types of jobs that are not running:

• Failed Jobs
• Broken Jobs
• Disabled Jobs
• Completed Jobs

See Also:

Failed Jobs

If a job has the status of FAILED in the job table, it was scheduled to run once but the execution has failed. If the job was specified as restartable, all retries have failed.

If a job fails in the middle of execution, only the last transaction of that job is rolled back. If your job executes multiple transactions, you need to be careful about setting restartable to TRUE. You can query failed jobs by querying the *_SCHEDULER_JOB_RUN_DETAILS views.

Broken Jobs

A broken job is one that has exceeded a certain number of failures. This number is set in max_failures, and can be altered. In the case of a broken job, the entire job is broken, and it will not be run until it has been fixed. For debugging and testing, you can use the RUN_JOB procedure.

You can query broken jobs by querying the *_SCHEDULER_JOBS and *_SCHEDULER_JOB_LOG views.

Disabled Jobs

A job can become disabled for the following reasons:

• The job was manually disabled
• The job class it belongs to was dropped
• The program, chain, or schedule that it points to was dropped
• A window or window group is its schedule and the window or window group is dropped

Completed Jobs

A job will be completed if end_date or max_runs is reached. (If a job recently completed successfully but is scheduled to run again, the job state is SCHEDULED.)

Job Recovery After a Failure

The Scheduler attempts to recover jobs that are interrupted when:

• The database abnormally shuts down
• A job slave process is killed or otherwise fails
• For an external job, the external job process that starts the executable or script is killed or otherwise fails. (The external job process is extjob on Unix. On Windows, it is the external job service.)
• For an external job, the process that runs the end-user executable or script is killed or otherwise fails.

Job recovery proceeds as follows:

• The Scheduler adds an entry to the job log for the instance of the job that was running when the failure occurred. In the log entry, the OPERATION is 'RUN', the STATUS is 'STOPPED', and ADDITIONAL_INFO contains one of the following:
  • REASON="Job slave process was terminated"
  • REASON="ORA-01014: ORACLE shutdown in progress"
• If restartable is set to TRUE for the job, the job is restarted.
• If restartable is set to FALSE for the job:
  • If the job is a run-once job and auto_drop is set to TRUE, the job run is done and the job is dropped.
  • If the job is a run-once job and auto_drop is set to FALSE, the job is disabled and the job state is set to 'STOPPED'.
  • If the job is a repeating job, the Scheduler schedules the next job run and the job state is set to 'SCHEDULED'.

When a job is restarted as a result of this recovery process, the new run is entered into the job log with the operation 'RECOVERY_RUN'.

Understanding Why a Program Becomes Disabled

A program can become disabled if a program argument is dropped or number_of_arguments is changed so that all arguments are no longer defined.

See "UsingPrograms" for more information regarding programs.

Understanding Why a Window Fails to Take Effect

A window can fail to take effect for the following reasons:

• A window becomes disabled when it is at the end of its schedule
• A window that points to a schedule that no longer exists is disabled

See "Using Windows" for more information regarding windows.

Examples of Using the Scheduler

This section discusses the following topics:

• Examples of Creating Jobs
• Examples of Creating Job Classes
• Examples of Creating Programs
• Examples of Creating Windows
• Examples of Setting Attributes
• Examples of Creating Chains
• Examples of Creating Jobs and Schedules Based on Events
• Example of Creating a Job In an Oracle Data Guard Environment

Examples of Creating Jobs

This section contains several examples of creating jobs. To create a job, you use the CREATE_JOB or the CREATE_JOBS procedures.

Example 28-1 Creating a Single Regular Job

The following statement creates a single regular job called my_job1 in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.my_job1',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                            ''sales''); END;',
   start_date           => '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY', 
   end_date             => '15-SEP-03 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/

This job gathers table statistics on the sales table. It will run for the first time on July 15th and then once a day until September 15. To verify that the job was created, issue the following statement:

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME
------------------------------
MY_JOB1

Example 28-2 Creating a Set of Regular Jobs

The following example creates a set of regular jobs:

DECLARE
 newjob sys.job;
 newjobarr sys.job_array;
BEGIN
 -- Create an array of JOB object types
 newjobarr := sys.job_array();

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

 -- Add definitions for 5 jobs
 FOR i IN 1..5 LOOP
   -- Create a JOB object type
   newjob := sys.job(job_name => 'TESTJOB' || to_char(i),
                     job_style => 'REGULAR',
                     job_template => 'PROG1',
                     repeat_interval => 'FREQ=MINUTELY;INTERVAL_15',
                     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;
/

Example 28-3 Creating a Set of Lightweight Jobs

The following example creates a set of lightweight jobs in one transaction:

DECLARE
 newjob sys.job;
 newjobarr sys.job_array;
BEGIN
 newjobarr := sys.job_array();
 newjobarr.extend(5);
 FOR i IN 1..5 LOOP
   newjob := sys.job(job_name => 'lwjob_' || to_char(i),
                     job_style => 'LIGHTWEIGHT',
                     job_template => 'PROG1',
                     repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
                     start_date => systimestamp + interval '10' second,
                     enabled => TRUE
                    );
   newjobarr(i) := newjob;
 end loop;

 DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
/

See Also:

Examples of Creating Job Classes

This section contains several examples of creating job classes. To create a job class, you use the CREATE_JOB_CLASS procedure.

Example 28-4 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              =>  'my_class1',
   service                     =>  'my_service1', 
   comments                    =>  'This is my first job class');
END;
/

This creates my_class1 in SYS. It uses a service called my_service1. To verify that the job class was created, issue the following statement:

SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES
WHERE JOB_CLASS_NAME = 'MY_CLASS1';

JOB_CLASS_NAME
------------------------------
MY_CLASS1

Example 28-5 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name             =>  'finance_jobs', 
   resource_consumer_group    =>  'finance_group',
   service                    =>  'accounting',
   comments                   =>  'All finance jobs');
END;
/

This creates finance_jobs in SYS. It assigns a resource consumer group called finance_group, and designates service affinity for the accounting service. Note that if the accounting service is mapped to a resource consumer group other than finance_group, jobs in this class run under the finance_group consumer group, because the resource_consumer_group attribute takes precedence.

See Also:

Examples of Creating Programs

This section contains several examples of creating programs. To create a program, you use the CREATE_PROGRAM procedure.

Example 28-6 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name          => 'oe.my_program1',
   program_type          => 'PLSQL_BLOCK',
   program_action        => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                             ''sales''); END;',
   number_of_arguments   => 0,
   enabled               => TRUE,
   comments              => 'My comments here');
END;
/

This creates my_program1, which uses PL/SQL to gather table statistics on the sales table. To verify that the program was created, issue the following statement:

SELECT PROGRAM_NAME FROM DBA_SCHEDULER_PROGRAMS 
WHERE PROGRAM_NAME = 'MY_PROGRAM1';

PROGRAM_NAME
-------------------------
MY_PROGRAM1

Example 28-7 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'oe.my_saved_program1',
   program_action         => '/usr/local/bin/date',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;
/

This creates my_saved_program1, which uses an executable.

See Also:

Examples of Creating Windows

This section contains several examples of creating windows. To create a window, you use the CREATE_WINDOW procedure.

Example 28-8 Creating a Window

The following statement creates a window called my_window1 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name          =>  'my_window1',
   resource_plan        =>  'my_res_plan1',
   start_date           =>  '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      =>  'FREQ=DAILY',
   end_date             =>  '15-SEP-03 1.00.00AM US/Pacific',
   duration             =>  interval '80' MINUTE,
   comments             =>  'This is my first window');
END;
/

This window will open once a day at 1AM for 80 minutes every day from May 15th to October 15th. To verify that the window was created, issue the following statement:

SELECT WINDOW_NAME FROM DBA_SCHEDULER_WINDOWS WHERE WINDOW_NAME = 'MY_WINDOW1';

WINDOW_NAME
------------------------------
MY_WINDOW1

Example 28-9 Creating a Window

The following statement creates a window called my_window2 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW ( 
   window_name       => 'my_window2',
   schedule_name     => 'my_stats_schedule',
   resource_plan     => 'my_resourceplan1',
   duration          => interval '60' minute,
   comments          => 'My window');
END; 
/

See Also:

Example of Creating Window Groups

This section contains an example of creating a window group. To create a window group, you use the CREATE_WINDOW_GROUP procedure.

Example 28-10 Creating a Window Group

The following statement creates a window group called my_window_group1:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_windowgroup1');
END;
/

Then, you could add three windows (my_window1, my_window2, and my_window3) to my_window_group1 by issuing the following statements:

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window1, my_window2');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window3');
END;
/

To verify that the window group was created and the windows added to it, issue the following statement:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME    ENABLED   NUMBER_OF_WINDOWS   COMMENTS
-----------------    -------   -----------------   ---------------
MY_WINDOW_GROUP1     TRUE                      3   This is my first window group

See Also:

Examples of Setting Attributes

This section contains several examples of setting attributes. To set attributes, you use SET_ATTRIBUTE and SET_SCHEDULER_ATTRIBUTE procedures.

Example 28-11 Setting the Repeat Interval Attribute

The following example resets the frequency my_emp_job1 will run to daily:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_emp_job1',
   attribute      =>   'repeat_interval',
   value          =>   'FREQ=DAILY');
END;
/

To verify the change, issue the following statement:

SELECT JOB_NAME, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME =  'MY_EMP_JOB1';

JOB_NAME             REPEAT_INTERVAL
----------------     ---------------
MY_EMP_JOB1          FREQ=DAILY

Example 28-12 Setting the Comments Attribute

The following example resets the comments for my_saved_program1:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_saved_program1',
   attribute      =>   'comments',
   value          =>   'For nightly table stats');
END;
/

To verify the change, issue the following statement:

SELECT PROGRAM_NAME, COMMENTS FROM DBA_SCHEDULER_PROGRAMS;

PROGRAM_NAME        COMMENTS
------------        -----------------------
MY_PROGRAM1         My comments here
MY_SAVED_PROGRAM1   For nightly table stats

Example 28-13 Setting the Duration Attribute

The following example resets the duration of my_window3 to 90 minutes:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_window3',
   attribute      =>   'duration',
   value          =>   interval '90' minute);
END;
/

To verify the change, issue the following statement:

SELECT WINDOW_NAME, DURATION FROM DBA_SCHEDULER_WINDOWS
WHERE WINDOW_NAME = 'MY_WINDOW3';

WINDOW_NAME        DURATION
-----------        ---------------
MY_WINDOW3         +000 00:90:00

Example 28-14 Setting the Database Role Attribute

The following example sets the database role of the job my_job to LOGICAL STANDBY.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name        => 'my_job',
   attribute   => 'database_role',
   value       =>'LOGICAL STANDBY');
END;
/

To verify the change in database role, issue the following command:

SELECT JOB_NAME, DATABASE_ROLE FROM DBA_SCHEDULER_JOB_ROLES
    WHERE JOB_NAME = 'MY_JOB';

JOB_NAME             DATABASE_ROLE
--------             -----------------
MY_JOB               LOGICAL STANDBY

Example 28-15 Setting the Event Expiration Attribute

The following example sets the time in seconds to 3600 when an event expires:

BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
   attribute     =>   event_expiry_time,
   value         =>   3600);
END;
/

Example 28-16 Setting Multiple Job Attributes for a Set of Regular Jobs

The following example sets four different attributes for each of the five regular jobs created in Example 28-2, "Creating a Set of Regular Jobs":

DECLARE
 newattr sys.jobattr;
 newattrarr sys.jobattr_array;
 j number;
BEGIN
 -- Create new JOBATTR array
 newattrarr := sys.jobattr_array();

 -- Allocate enough space in the array
 newattrarr.extend(20);
 j := 1;
 FOR i IN 1..5 LOOP
   -- Create and initialize a JOBATTR object type
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'MAX_FAILURES',
                          attr_value => 5);
   -- Add it to the array.
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'COMMENTS',
                          attr_value => 'Bogus comment');
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'END_DATE',
                          attr_value => systimestamp + interval '24' hour);
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'SCHEDULE_LIMIT',
                          attr_value => interval '1' hour);
   newattrarr(j) := newattr;
   j := j + 1;
 END LOOP;

 -- Call SET_JOB_ATTRIBUTES to set all 20 set attributes in one transaction
 DBMS_SCHEDULER.SET_JOB_ATTRIBUTES(newattrarr, 'TRANSACTIONAL');
END;
/

Example 28-17 Setting Attributes for a Set of Lightweight Jobs

The following example sets multiple attributes for a set of lightweight jobs. Note that not all regular job attributes are supported for lightweight jobs:

DECLARE
 newattr sys.jobattr;
 newattrarr sys.jobattr_array;
 j number;
BEGIN
 -- Create new JOBATTR array
 newattrarr := sys.jobattr_array();

 -- Allocate enough space in the array
 newattrarr.extend(10);
 j := 1;
 FOR i IN 1..5 LOOP
   -- Create and initialize JOBATTR object type
   newattr := sys.jobattr(job_name => 'lwjob_' || to_char(i),
                          attr_name => 'END_DATE',
                          attr_value => systimestamp + interval '24' hour);
   -- Add it to array.
   newattrarr(j) := newattr;
   j := j + 1;

   newattr := sys.jobattr(job_name => 'lwjob_' || to_char(i),
                          attr_name => 'RESTARTABLE',
                          attr_value => TRUE);
   newattrarr(j) := newattr;
   j := j + 1;
 END LOOP;

 -- Call SET_JOB_ATTRIBUTES to set all 20 set attributes in one transaction
 DBMS_SCHEDULER.SET_JOB_ATTRIBUTES(newattrarr, 'TRANSACTIONAL');
END;
/

See Also:

Examples of Creating Chains

This section contains examples of creating chains. To create chains, you use the CREATE_CHAIN procedure. After creating a chain, you add steps to the chain with the DEFINE_CHAIN_STEP procedure and define the rules with the DEFINE_CHAIN_RULE procedure.

Example 28-18 Creating a Chain

The following example creates a chain where my_program1 runs before my_program2 and my_program3. my_program2 and my_program3 run in parallel after my_program1 has completed.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'my_chain1',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step3', 'my_program3');
END;
/

--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step1 COMPLETED', 'Start step2, step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step2 COMPLETED AND step3 COMPLETED', 'END');
END;
/

Example 28-19 Creating a Chain

The following example creates a chain where first my_program1 runs. If it succeeds, my_program2 runs; otherwise, my_program3 runs.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            => 'my_chain2',
   rule_set_name         => NULL,
   evaluation_interval   => NULL,
   comments              => NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step3', 'my_program3');
END;
/
 
--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain2', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 SUCCEEDED', 'Start step2');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 COMPLETED AND step1 NOT SUCCEEDED', 'Start step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step2 COMPLETED OR step3 COMPLETED', 'END');
END;
/

See Also:

Examples of Creating Jobs and Schedules Based on Events

This section contains examples of creating event-based jobs and event schedules. To create event-based jobs, you use the CREATE_JOB procedure. To create event-based schedules, you use the CREATE_EVENT_SCHEDULE procedure.

These examples assume the existence of an application that, when it detects the arrival of a file on a system, enqueues an event onto the queue my_events_q.

Example 28-20 Creating an Event-Based Schedule

The following example illustrates creating a schedule that can be used to start a job whenever the Scheduler receives an event indicating that a file arrived on the system before 9AM:

BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'scott.file_arrival',
   start_date        =>  systimestamp,
   event_condition   =>  'tab.user_data.object_owner = ''SCOTT'' 
      and tab.user_data.event_name = ''FILE_ARRIVAL'' 
      and extract hour from tab.user_data.event_timestamp < 9',
   queue_spec        =>  'my_events_q');
END;
/

Example 28-21 Creating an Event-Based Job

The following example creates a job that starts when the Scheduler receives an event indicating that a file arrived on the system:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  my_job,
   program_name        =>  my_program,
   start_date          =>  '15-JUL-04 1.00.00AM US/Pacific',
   event_condition     =>  'tab.user_data.event_name = ''FILE_ARRIVAL''',
   queue_spec          =>  'my_events_q'
   enabled             =>  TRUE,
   comments            =>  'my event-based job');
END;
/

See Also:

Example of Creating a Job In an Oracle Data Guard Environment

In an Oracle Data Guard environment, the Scheduler includes additional support for two database roles: primary and logical standby. You can configure a job to run only when the database is in the primary role or only when the database is in the logical standby role. To do so, you set the database_role attribute. This example explains how to enable a job to run in both database roles. The method used is to create two copies of the job and assign a different database_role attribute to each.

By default, a job runs when the database is in the role that it was in when the job was created. You can run the same job in both roles using the following steps:

1. Copy the job
2. Enable the new job
3. Change the database_role attribute of the new job to the required role

The example starts by creating a job called primary_job on the primary database. It then makes a copy of this job and sets its database_role attribute to 'LOGICAL STANDBY'. If the primary database then becomes a logical standby, the job continues to run according to its schedule.

When you copy a job, the new job is disabled, so you must enable the new job.

BEGINDBMS_SCHEDULER.CREATE_JOB (
     job_name       => 'primary_job',
     program_name   => 'my_prog',
     schedule_name  => 'my_sched');

DBMS_SCHEDULER.COPY_JOB('primary_job','standby_job');
DBMS_SCHEDULER.ENABLE(name=>'standby_job', commit_semantics=>'ABSORB_ERRORS');
DBMS_SCHEDULER.SET_ATTRIBUTE('standby_job','database_role','LOGICAL STANDBY');
END;
/

After you execute this example, the data in the DBA_SCHEDULER_JOB_ROLES view is as follows:

SELECT JOB_NAME, DATABASE_ROLE FROM DBA_SCHEDULER_JOB_ROLES
   WHERE JOB_NAME IN ('PRIMARY_JOB','STANDBY_JOB');

JOB_NAME               DATABASE_ROLE
--------               ----------------
PRIMARY_JOB            PRIMARY
STABDBY_JOB            LOGICAL STANDBY

Note:

Scheduler Reference

This section contains reference information for Oracle Scheduler. It contains the following topics:

• Scheduler Privileges
• Scheduler Data Dictionary Views

Scheduler Privileges

Table 28-2 lists the various Scheduler privileges.

Table 28-2 Scheduler Privileges

The SCHEDULER_ADMIN role is created with all of the system privileges shown in Table 28-2 (with the ADMIN option). The SCHEDULER_ADMIN role is granted to DBA (with the ADMIN option).

The following object privileges are granted to PUBLIC: SELECT ALL_SCHEDULER_* views, SELECT USER_SCHEDULER_* views, SELECT SYS.SCHEDULER$_JOBSUFFIX_S (for generating a job name), and EXECUTE SYS.DEFAULT_JOB_CLASS.

Scheduler Data Dictionary Views

You can check Scheduler information by using many views. An example is the following, which shows information for completed instances of my_job1:

SELECT JOB_NAME, STATUS, ERROR#
FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME     STATUS           ERROR#
--------     --------------   ------
MY_JOB1      FAILURE           20000

Table 28-3 contains views associated with the Scheduler. The *_SCHEDULER_JOBS, *_SCHEDULER_SCHEDULES, *_SCHEDULER_PROGRAMS, *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, *_SCHEDULER_JOB_RUN_DETAILS views are particularly useful for managing jobs. See Oracle Database Reference for details regarding Scheduler views.

Note:

Table 28-3 Scheduler Views

*_SCHEDULER_SCHEDULES

*_SCHEDULER_PROGRAMS

*_SCHEDULER_PROGRAM_ARGS

*_SCHEDULER_JOBS

*_SCHEDULER_RUNNING_CHAINS

*_SCHEDULER_CHAIN_STEPS

*_SCHEDULER_CHAINS

*_SCHEDULER_CHAIN_RULES

*_SCHEDULER_GLOBAL_ATTRIBUTE

*_SCHEDULER_JOB_ARGS

*_SCHEDULER_JOB_CLASSES

*_SCHEDULER_WINDOWS

*_SCHEDULER_JOB_RUN_DETAILS

*_SCHEDULER_WINDOW_GROUPS

*_SCHEDULER_WINGROUP_MEMBERS

*_SCHEDULER_RUNNING_JOBS

*_SCHEDULER_JOB_LOG

*_SCHEDULER_WINDOW_LOG

*_SCHEDULER_WINDOW_DETAILS

*_SCHEDULER_CREDENTIALS

*_SCHEDULER_JOB_ROLES

NEWS CONTENTS

• 200102 : Oracle 10g Scheduler Enhancements, Part 2- Job Chains ( Oracle 10g Scheduler Enhancements, Part 2- Job Chains , )
• 200102 : References and Additional Reading ( )
• 200102 : Oracle Scheduler Overview ( Oracle Scheduler Overview, )

Old News ;-)

Oracle 10g Scheduler Enhancements, Part 2- Job Chains

By Jim Czuprynski

Synopsis. Oracle 10g Release 1 added a new and powerful scheduling tool – aptly named the Scheduler – that significantly augments an Oracle DBA's abilities to schedule and control just about any type of frequently repeating task from within an Oracle 10g database. The final article in this series illustrates how an Oracle DBA can use the new Job Chain capabilities of the Scheduler in Oracle 10g Release 2 (10gR2) to schedule and trigger multiple interrelated and dependent tasks based upon specific yet complex sets of processing rules.

... ... ...

The good news is that Oracle 10gR2 has strengthened the Oracle Scheduler so that it can handle complex batch scheduling with the addition of a new Scheduler object called the job chain. Each job chain is comprised of one or more chain steps, and the relationships between the chain steps are managed by chain rules. For a practical demonstration of the power of the job chain, I will use the following scenario to satisfy one of the requirements I mentioned in the previous article in this series: I need to detect the arrival of a new vendor-supplied external file that contains accounting information (in this scenario, employee paychecks). The file is supposed to arrive twice a month before the bimonthly payroll cycle begins; however, during recent payroll processing cycles, this file has arrived unexpectedly late or early. Once the file arrives, it must be validated based on specific business rules. If any of these validations fail, all further processing must halt, and a notification needs to be sent to the DBA that the job has failed. Once the file is validated, its contents must be loaded into a database table. If any DML errors occur during the load – for instance, if a column's size is exceeded, or if a check constraint is violated - then the job needs to notify the appropriate accounting personnel. Once the file's contents have been loaded, I need to notify the appropriate business unit of its successful processing, and send along a summary of how many records were processed. If there are any post-loading error conditions – for example, if the employee's Social Security contributions for the year have exceeded the mandated maximum – then I need to notify the appropriate accounting personnel as well. I have summarized these business rules in the following flowchart. Figure 1. Bimonthly Employee Payroll Check External File Processing Flow To implement these requirements, I first need to build the infrastructure to handle the incoming payroll checks. I will start by creating an external table, HR.XT_EMPLOYEE_PAYROLL_CHECKS, that describes the layout of the incoming file. I will use this external table for two purposes: to run validation queries against the incoming data before loading it, and to read from the file during the loading process. Listing 2.1 shows the code I used to create the external table, as well as the creation of the DIRECTORY object that Oracle needs to locate the flat file. I will next create a standard database table, HR.EMPLOYEE_PAYROLL_CHECKS, that will be used to store each payroll check that passes validation. The code in Listing 2.2 shows how to create this table and its dependent indexes and constraints. Finally, I will create a package named HR.PAYROLL_PROCESSING that encapsulates all business logic for processing of employee paychecks. I will call this package's functions and procedures within my job chain's steps to validate the incoming file, load its data into the database, and perform post-loading validation. Note that I am also using DBMS_ERRLOG.CREATE_ERROR_LOG to create a DML error logging table that will trap any DML errors that might occur when loading the HR.EMPLOYEE_PAYROLL_CHECKS table. This is a brand-new feature in Oracle 10gR2 that makes it simple to capture unexpected data issues when inserting, updating or deleting rows. Listing 2.3 shows the code to create the DML error logging table, package specification, and package body. Chain Steps and Chain Rules Now that the infrastructure is completed, I will turn my attention to implementing the job chain via the two job chain components, chain steps and chain rules. As its name suggests, a chain step groups together similar business processes into a larger logical unit of work. As each chain step completes its processing, Oracle records the status of the step in a special queue, SCHEDULER$_QUEUE. Scheduler jobs, job chains or even chain steps within the same job chain can interrogate this queue so that other dependent business processes can be triggered. Chain rules, on the other hand, define when a chain step should begin its processing, which chain step(s) should commence upon success of a specific chain step, and what chain step(s) should commence upon failure of a specific chain step. Starting a Chain Based on an Event Like the standard, public SYS.AQ_EVENT and SCHEDULER$_QUEUE event queues, I also have the capability to create a private event queue reserved for publishing (or enqueueing) a specific message – for example, the arrival of the bimonthly payroll checks file. And just like any event queue, a private event queue can be read by other Scheduler objects and thus used to trigger other Scheduler tasks. Via the code in Listing 2.4, I will establish a new queue, SYS.FILE_ARRIVAL_EVENT_Q, into which I will eventually queue a message when the new Employee Paycheck file has arrived. I will also create a new agent, AGT_FILE_EVENT_MONITOR, that is responsible for watching the new queue for the arrival of the new file. Note that I have granted specific privileges to the HR user account so that it can both post a message to the file arrival event queue and read from the queue to determine if the file has arrived and if the job chain can commence. Creating a Job Chain Next, I will tackle the creation of the Job Chain and its components. First, I will create a new job chain Scheduler object, BIMONTHLY_PAYROLL_PROCESSING, that is owned by the HR schema via a call to the DBMS_SCHEDULER.CREATE_CHAIN procedure. Once that is in place, I will then create job chain step Scheduler objects. Note that the first step in the chain, PAYROLL_VALIDATION, is defined with procedure DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP. This procedure relies upon a special Scheduler schedule object, HR.MONITOR_PAYCHECK_ARRIVAL, that will watch for the arrival of the file and then allow the job chain to begin its processing. The remaining job chain steps will be defined via the DBMS_SCHEDULER.DEFINE_CHAIN_STEP procedure. These chain steps will then call corresponding Scheduler program objects to handle various phases of the payroll checks file processing. Once all the chain steps are created, I will build job chain rule Scheduler objects that determine what should happen upon success or failure of each job chain step using the DBMS_SCHEDULER.DEFINE_CHAIN_RULE procedure. Listing 2.5 shows the code required to create the job chain, chain steps, and chain step rules, as well as the three Scheduler program objects that process the Employee Payroll checks file. Starting a Chain via a Scheduled Job I will now create the previously mentioned HR.MONITOR_PAYCHECK_ARRIVAL Scheduler schedule object. This object will monitor the SYS.FILE_ARRIVAL_EVENT_Q via the SYS.AGT_FILE_ARRIVAL_MONITOR agent and, when the requested external file arrives, this schedule will signal the job chain to start its processing scheme. Note that I could use the file arrival event queue to register the arrival of many other external files in any schema, but this schedule object will activate itself only when the Employee Payroll Checks file arrives. Since all of the Job Chain pieces are in place, I will activate the chain via a call to the DBMS_SCHEDULER.ENABLE procedure. Finally, since every job chain must be started by a call from a Scheduler job object, I will create a new job, HR.CHN_START_PAYROLL_PROCESSING, that will start to run a day before the anticipated arrival of the Employee Payroll Checks file and will continue to run until either (a) the file arrives, or (b) 48-hours have passed. If the file never arrives, the job will simply never run. I have included the code to complete the job chain setup in Listing 2.6. Proof of Concept: Starting the Job Chain upon File Arrival All the pieces are in place, so it is time for an actual demonstration. Via the code in Listing 2.7 I will initiate the HR.CHN_START_PAYROLL_PROCESSING outside of its normal schedule by invoking the DBMS_SCHEDULER.RUN_JOB procedure. Even though the job chain is now started, it will continue to wait until either (a) its first job chain step detects the arrival of the employee payroll checks file, or (b) the job itself runs out of time to execute (based on its defined 48-hour schedule). First, I will place a test copy of the Employee Payroll Checks file into the appropriate directory, and then I will run the code in the anonymous PL/SQL block in Listing 2.7. This block of code queues a message in the SYS.FILE_ARRIVAL_EVENT_Q queue to simulate the file's arrival. The event will be detected by the PAYROLL_VALIDATION job chain step, and the job will then commence its processing in earnest. Obviously, unit testing a job chain with greater complexity than this example could easily become a labor-intensive nightmare. Fortunately, the Scheduler provides another unit-testing option: DBMS_SCHEDULER.RUN_CHAIN. This procedure lets me specify one or more job chain steps to be started, and the RUN_CHAIN will start the job chain steps and allow the job to continue to its conclusion. Even better, this procedure is overloaded so that I can also specify one or more steps to start, but also include their current state (e.g. FAILED, SUCCEEDED) so that I can easily test out chain step rule processing. I have provided an example of both invocations of this procedure in Listing 2.8. As the flowchart in Figure 1 shows, there are at least three points in this job chain's processing flow at which a failure could occur and trigger an expected alternative to successful job completion. The sample data I have included to demonstrate these examples contain seven rows that have unacceptable values for the HR.EMPLOYEE_PAYROLL_CHECKS.STATUS_IND column. These rows will be logged to the HR.PAYROLL_PROCESSING_ERRORS DML error logging table, and this triggers a failure of the job chain step LOAD_PAYROLL_CHECKS. When a Scheduler job chain is initiated, the Oracle Scheduler actually creates a one-time-only job whose job name corresponds to that of each chain step. These jobs can be easily tracked either via the Enterprise Manager Database Control Scheduler, or simply by running a few queries against DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. I have included a few sample reports from my unit testing results in Listing 2.9. Viewing Job Chain Metadata Four new data dictionary views describe the Scheduler's new job chain components: DBA_SCHEDULER_CHAINS shows all current job chains. DBA_SCHEDULER_CHAIN_STEPS shows the corresponding job chain steps. DBA_SCHEDULER_CHAIN_RULES shows the corresponding chain step rules. Finally, DBA_SCHEDULER_RUNNING_CHAINS shows the current status of any job chains that are running at this moment. Listing 2.10 shows several queries that can be used to view the contents of these new views. Conclusion Oracle 10gR2 has made significant improvements to the flexibility and versatility of the Oracle Scheduler with new capabilities to combine multiple independent schedules into one coherent schedule, trigger a job based on the status of a queued event, and control and process complex business rules and relationships with job chains, chain steps, and chain rules. These new features raise the bar for intra-database scheduling capabilities to a new height, and demand the serious attention of any Oracle DBA as effective alternatives to operating system-based or third-party scheduling tools. References and Additional Reading Even though I have hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I have drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article: B14214-01 Oracle Database New Features Guide B14229-01 Oracle Streams Concepts and Administration B14231-01 Oracle Database Administrator's Guide B14257-01 Oracle Streams Advanced Queuing User's Guide and Reference B14258-01 PL/SQL Packages and Types Reference " See All Articles by Columnist Jim Czuprynski

Oracle Scheduler Overview

Scheduler Overview

As a database feature the Oracle Scheduler has several advantages:

• Leverage existing database knowledge
• DBAs have total control over what runs on the database and when
• The Scheduler is platform independent. Therefore jobs can be managed similarly on all platforms. Jobs can be easily moved from one system to another, for example from a development environment to production, using the EXPORT/IMPORT utility in the database

The Scheduler can execute OS jobs (shell scripts, executables etc.), PL/SQL blocks, and PL/SQL or Java stored procedures. OS jobs can run as any OS user on or across different operating platforms, such as Unix, Windows, z/OS and OS/400. It can be accessed using a Web Interface (EM) as well as an API - DBMS_SCHEDULER. The Web Interface makes the Scheduler accessible from anywhere using a web browser. The Scheduler provides complex enterprise scheduling functionality. You can use this functionality to do the following:

• Schedule Job Execution
• Define Multi-Step jobs
• Schedule Job Processing that Models Business Requirements
• Manage and Monitor Jobs
• Execute and Manage Jobs in a Clustered Environment

Schedule Job Execution

The most basic capability of a job scheduler is to schedule the execution of a job. The Scheduler supports both time-based and event-based scheduling.

Time-based scheduling

Time-based scheduling enables users to specify a fixed date and time (for example, Jan. 23rd 2006 at 1:00 AM), a repeating schedule (for example, every Monday ), or a defined rule (for example the last Sunday of every other month or the fourth Thursday in November which defines Thanksgiving).

Users can create new composite schedules with minimum effort by combining existing schedules. For example if a HOLIDAY and WEEKDAY schedule were already defined, a WORKDAY schedule can be easily created by excluding the HOLIDAY schedule from the WEEKDAY schedule.

Companies often use a fiscal calendar as opposed to a regular calendar and thus have the requirement to schedule jobs on the last workday of their fiscal quarter. The Scheduler supports user-defined frequencies which enables users to define not only the last workday of every month but also the last workday of every fiscal quarter.

Event-based scheduling

Event-based scheduling as the name implies triggers jobs based on real-time events. Events are defined as any state changes or occurrences in the system such as the arrival of a file. Scheduling based on events enables you to handle situations where a precise time is not known in advance for when you would want a job to execute.

Define Multi-step jobs

The Scheduler has support for single or multi-step jobs. Multi-step jobs are defined using a Chain. A Chain consists of multiple steps combined using dependency rules. Since each step represents a task, Chains enable users to specify dependencies between tasks, for example execute task C one hour after the successful completion of task A and task B.

Schedule Job Processing that Models Business Requirements

The Scheduler enables job processing in a way that models your business requirements. It enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. Jobs that share common characteristic and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This lets you ensure that critical jobs have priority and enough resources to complete. Jobs can also be prioritized within a job class.

The Scheduler also provides the ability to change the prioritization based on a schedule. Because the definition of a critical job can change across time, the Scheduler lets you define different class priorities at different times.

Manage and Monitor Jobs

There are multiple states that a job undergoes from its creation to its completion. All Scheduler activity is logged, and information, such as the status of the job and the time to completion, can be easily tracked. This information is stored in views. It can be queried with Enterprise Manager or a SQL query. The views provide information about jobs and their execution that can help you schedule and manage your jobs better. For example, you can easily track all jobs that failed for user SCOTT.

In order to facilitate the monitoring of jobs, users can also flag the Scheduler to raise an event if unexpected behavior occurs and indicate the actions that should be taken if the specified event occurs. For example if a job failed an administrator should be notified.

Execute and Manage Jobs in a Clustered Environment

A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the service where you want a job to run.

Recommended Links

Overview of Oracle Scheduler

Oracle Scheduler Overview
http://www.oracle.com/technology/products/database/scheduler/htdocs/scheduler_fov.html - 60k

Scheduler FAQ (May 5 2004 -- outdated)

[PDF] Microsoft Word - Scheduler_twp_11G.doc
http://www.oracle.com/technology/products/dataint/pdf/scheduler.pdf - 261k - Aug 10, 2007

• Overview of the Scheduler
• DBMS_SCHEDULER
• Oracle Job Scheduling
• Scheduler Enhancements in Oracle Database 10g Release 2
• Scheduler Enhancements in Oracle Database 11g Release 1
• Scheduler Enhancements in Oracle Database 11g Release 2

• Job Coordination Using DBMS_JOB and DBMS_PIPE -- Slides
• "Leveraging Oracle's in-built Job Scheduler" - Examples of complex time-based schedules
• New Scheduler Utilities - Mike Ault's Oracle 10g New Features book

ORACLE-BASE - Scheduler in Oracle Database 10g

Database journal

Part 1: Overview

Part 2: Implementation

Part 3: Advanced Features

Oracle 10g Scheduler Enhancements, Part 2 Job Chains - DatabaseJournal.com

Oracle Scheduler Training

• Oracle by Example: Scheduling jobs
• Oracle by Example: Administering the Oracle Scheduler
• Oracle iLearning: Oracle 10g Scheduler - A Technical Overview (Duration 87 min) - Presentation + Demo
• Oracle chat: An overview of the Scheduler feature in 10g (Duration 47 min)

Oracle Scheduler Utilities

• File Arrival Package: The file arrival package enables a user to trigger jobs based on the arrival of a file in a specified directory
• Job Notification Package:The job notification package enables users to setup e-mail notification for jobs

Oracle scheduler advisory board

Oracle Scheduler

Recommended Books

Oracle Database Administrator's Guide

In this chapter:

• Overview of Oracle Scheduler
• Programs
• Schedules
• Jobs
• Chains
• Job Classes
• Windows
• Window Groups
• Scheduler Architecture
• Scheduler Support for Oracle Data Guard

Oracle Job Scheduling: Creating Robust Task Management with dbms_job and Oracle 10g dbms_scheduler (Oracle In-Focus series)

In answer to some previous reviews, the book was written against 8i, 9i and 10gR1. The book was finished and with the publishers before the release of 10g Release 2, so it doesn't contain event based scheduling.

The scheduler updates in 10gR2 are explained on my website.

Note: Tim Hall is the brains behind http://www.oracle-base.com and has produced more tips and explanations over the years than most in the Oracle community.

Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month :  How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: March 12, 2019