|
Home | Switchboard | Unix Administration | Red Hat | TCP/IP Networks | Neoliberalism | Toxic Managers |
(slightly skeptical) Educational society promoting "Back to basics" movement against IT overcomplexity and bastardization of classic Unix |
|
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:
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.
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:
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".
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.
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:
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
Supported platforms
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:
Table 27-1 illustrates common job tasks and their appropriate procedures and privileges:
Task | Procedure | Privilege Needed |
---|---|---|
Create a job | CREATE_JOB or CREATE_JOBS |
CREATE JOB or CREATE ANY
JOB |
Alter a job | SET_ATTRIBUTE or SET_JOB_ATTRIBUTES |
ALTER or CREATE ANY JOB or be
the owner |
Run a job | RUN_JOB |
ALTER or CREATE ANY JOB or be
the owner |
Copy a job | COPY_JOB |
ALTER or CREATE ANY JOB or be
the owner |
Drop a job | DROP_JOB |
ALTER or CREATE ANY JOB or be
the owner |
Stop a job | STOP_JOB |
ALTER or CREATE ANY JOB or be
the owner |
Disable a job | DISABLE |
ALTER or CREATE ANY JOB or be
the owner |
Enable a job | ENABLE |
ALTER or CREATE ANY JOB or be
the owner |
See "Scheduler Privileges" for further information regarding privileges.
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
).
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.
After creating a job, you may need to set job arguments if:
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:
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.
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:
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; /
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; /
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; /
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:
CREATE_JOB
procedure.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');
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.
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.
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:
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.
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".
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.
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.
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
.
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.
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:
TheADDITIONAL_INFO
column can have multiple name/value pairs. The order is indeterminate, so you must parse the field
to locate the STANDARD_ERROR
name/value pair.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.
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:
When a job is stopped, only the current transaction is rolled back. This can cause data inconsistency.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.
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.
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.
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.
A program is a collection of metadata about a particular task. This section introduces you to basic program tasks, and discusses the following topics:
See Also:
"Programs" for an overview of programs.Table 27-2 illustrates common program tasks and their appropriate procedures and privileges:
Table 27-2 Program Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a program | CREATE_PROGRAM |
CREATE JOB or CREATE ANY
JOB |
Alter a program | SET_ATTRIBUTE |
ALTER or CREATE ANY JOB or
be the owner |
Drop a program | DROP_PROGRAM |
ALTER or CREATE ANY JOB or
be the owner |
Disable a program | DISABLE |
ALTER or CREATE ANY JOB or
be the owner |
Enable a program | ENABLE |
ALTER or CREATE ANY JOB or
be the owner |
See "Scheduler Privileges" for further information regarding privileges.
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; /
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 theDEFINE_PROGRAM_ARGUMENT
, DEFINE_ANYDATA_ARGUMENT
, and
DEFINE_METADATA_ARGUMENT
proceduresYou 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:
The Scheduler Programs page appears. It displays existing programs.
The Edit Program page appears.
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.
If any currently running jobs use the program that you altered, they continue to run with the program as defined before the alter operation.
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.
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.
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.
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:
See Also:
"Schedules" for an overview of schedules.Table 27-3 illustrates common schedule tasks and the procedures you use to handle them.
Table 27-3 Schedule Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a schedule | CREATE_SCHEDULE |
CREATE JOB or CREATE ANY
JOB |
Alter a schedule | SET_ATTRIBUTE |
ALTER or CREATE ANY JOB or
be the owner |
Drop a schedule | DROP_SCHEDULE |
ALTER or CREATE ANY JOB or
be the owner |
See "Scheduler Privileges" for further information regarding privileges.
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.
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.
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.
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:
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.
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.
The following are important differences in behavior between a calendaring expression and PL/SQL repeat interval:
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.
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 ...
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.
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:
See Also:
"Job Classes" for an overview of job classes.Table 27-4 illustrates common job class tasks and their appropriate procedures and privileges:
Table 27-4 Job Class Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a job class | CREATE_JOB_CLASS |
MANAGE SCHEDULER |
Alter a job class | SET_ATTRIBUTE |
MANAGE SCHEDULER |
Drop a job class | DROP_JOB_CLASS |
MANAGE SCHEDULER |
See "Scheduler Privileges" for further information regarding privileges.
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.
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".
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.
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:
This controls when the window is in effect.
This controls how long the window is open.
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:
See Also:
"Windows" for an overview of windows.Table 27-5 illustrates common window tasks and the procedures you use to handle them.
Table 27-5 Window Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a window | CREATE_WINDOW |
MANAGE SCHEDULER |
Open a window | OPEN_WINDOW |
MANAGE SCHEDULER |
Close a window | CLOSE_WINDOW |
MANAGE SCHEDULER |
Alter a window | SET_ATTRIBUTE |
MANAGE SCHEDULER |
Drop a window | DROP_WINDOW |
MANAGE SCHEDULER |
Disable a window | DISABLE |
MANAGE SCHEDULER |
Enable a window | ENABLE |
MANAGE SCHEDULER |
See "Scheduler Privileges" for further information regarding privileges.
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:
The Scheduler Windows page appears. It displays existing windows.
The Create Window page appears.
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.
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.
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".
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:
OPEN_WINDOW
procedureThis 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.
There are two ways a window can close:
A window will close based on the schedule defined at creation time.
CLOSE_WINDOW
procedureThe 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.
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.
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.
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.
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:
Whenever two windows overlap, an entry is written in the Scheduler log.
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:
No windows are open, so a default resource plan is in effect.
Window1 has been assigned a low priority, but it opens because there are no high priority windows. Therefore, Resource Plan 1 is in effect.
Window3 will open because it has a higher priority than Window1, so Resource Plan 3 is in effect.
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.
A default resource plan is in effect because no windows are open.
Window2 will open so Resource Plan 2 is in effect.
Window4 is of the same priority as Window2, so it will not interrupt Window2. Therefore, Resource Plan 2 is in effect.
Window4 will open so Resource Plan 4 is in effect.
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:
A default resource plan is in effect.
Window1 has been assigned a low priority, but it opens because there are no high priority windows, so Resource Plan 1 is in effect.
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.
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.
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:
See Also:
"Window Groups" for an overview of window groups.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
Task | Procedure | Privilege Needed |
---|---|---|
Create a window group | CREATE_WINDOW_GROUP |
MANAGE SCHEDULER |
Drop a window group | DROP_WINDOW_GROUP |
MANAGE SCHEDULER |
Add a member to a window group | ADD_WINDOW_GROUP_MEMBER |
MANAGE SCHEDULER |
Drop a member to a window group | REMOVE_WINDOW_GROUP_MEMBER |
MANAGE SCHEDULER |
Enable a window group | ENABLE |
MANAGE SCHEDULER |
Disable a window group | DISABLE |
MANAGE SCHEDULER |
See "Scheduler Privileges" for further information regarding privileges.
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.
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.
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.
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.
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.
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
The Scheduler works with two kinds of events:
This section provides details on how to work with both kinds of events, and includes the following topics:
See Also:
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
Task | Procedure | Privilege Needed |
---|---|---|
Altering a Job to Raise Events |
SET_ATTRIBUTE |
CREATE ANY JOB or ownership of job being
altered or ALTER privileges on the job |
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.
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:
The discussion ofDBMS_SCHEDULER
.SET_ATTRIBUTE
in
Oracle Database PL/SQL Packages and Types Reference
for the names and values of job state bit flagsTo 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:
SYS
user or as a user with the MANAGE
ANY
QUEUE
privilege.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), );
Attribute | Description |
---|---|
event_type |
One of "JOB_STARTED ", "JOB_SUCCEEDED ", "JOB_FAILED ",
"JOB_BROKEN ", "JOB_COMPLETED ", "JOB_STOPPED ", "JOB_SCH_LIM_REACHED ", "JOB_DISABLED ",
"JOB_CHAIN_STALLED ", "JOB_OVER_MAX_DUR ".
For descriptions of these event types, see the Constants section for the |
object_owner |
Owner of the job that raised the event. |
object_name |
Name of the job that raised the event. |
event_timestamp |
Time at which the event occurred. |
error_code |
Applicable only when an error is thrown during job execution. Contains the top-level error code. |
error_msg |
Applicable only when an error is thrown during job execution. Contains the entire error stack. |
event_status |
Adds further qualification to the event type. If event_type is "JOB_STARTED ,"
a status of 1 indicates that it is a normal start, and a status of 2 indicates that it is a retry.
If If |
log_id |
Points to the ID in the scheduler job log from which additional information can
be obtained. Note that there need not always be a log entry corresponding to an event. In such cases, log_id is
NULL . |
run_count |
Run count for the job when the event was raised. |
failure_count |
Failure count for the job when the event was raised. |
retry_count |
Retry count for the job when the event was raised. |
spare1 – spare8 |
Currently not implemented |
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:
The Scheduler runs the event-based job for each occurrence of an event that matchesevent_condition
. However, events that
occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.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
Task | Procedure | Privilege Needed |
---|---|---|
Creating an Event-Based Job |
CREATE_JOB |
CREATE JOB or CREATE ANY
JOB |
Altering an Event-Based Job |
SET_ATTRIBUTE |
CREATE ANY JOB or ownership of the job being
altered or ALTER privileges on the job |
Creating an Event Schedule |
CREATE_EVENT_SCHEDULE |
CREATE JOB or CREATE ANY
JOB |
Altering an Event Schedule |
SET_ATTRIBUTE |
CREATE ANY JOB or ownership of the schedule
being altered or ALTER privileges on the schedule |
See Also:
Oracle Streams Advanced Queuing User's Guide for information on how to create queues and enqueue messages.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.
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.
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.
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.
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
.
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.
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:
STORED_PROCEDURE
.metadata_attribute
set to EVENT_MESSAGE
.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 ; /
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:
Step | See... |
---|---|
1. Create a chain object | Creating Chains |
2. Define the steps in the chain | Defining Chain Steps |
3. Add rules | Adding Rules to a Chain |
4. Enable the chain | Enabling Chains |
5. Create a job that points to the chain | Creating Jobs for Chains |
Other topics discussed in this section include:
See Also:
Table 27-9 illustrates common tasks involving chains and the procedures associated with them.
Table 27-9 Chain Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a chain | CREATE_CHAIN |
CREATE JOB , CREATE EVALUATION CONTEXT and CREATE
RULE SET if the owner. CREATE ANY JOB , CREATE ANY RULE SET and CREATE ANY EVALUATION
CONTEXT otherwise |
Drop a chain | DROP_CHAIN |
Ownership of the chain or ALTER privileges on the chain or CREATE
ANY JOB privileges. If not owner, also requires DROP ANY EVALUATION CONTEXT and DROP ANY RULE SET |
Alter a chain | ALTER_CHAIN |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Alter a chain | SET_ATTRIBUTE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Alter a running chain | ALTER_RUNNING_CHAIN |
Ownership of the job, or ALTER privileges on the job or CREATE
ANY JOB |
Run a chain | RUN_CHAIN |
CREATE JOB or CREATE ANY JOB . In addition, the owner
of the new job must have EXECUTE privileges on the chain or EXECUTE ANY PROGRAM |
Add rules to a chain | DEFINE_CHAIN_RULE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB privileges. CREATE RULE if the owner of the chain, CREATE ANY RULE otherwise |
Alter rules in a chain | DEFINE_CHAIN_RULE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB privileges. If not owner of the chain, requires ALTER privileges on the rule or ALTER
ANY RULE |
Drop rules from a chain | DROP_CHAIN_RULE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB privileges. DROP ANY RULE if not the owner of the chain |
Enable a chain | ENABLE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Disable a chain | DISABLE |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Create steps | DEFINE_CHAIN_STEP |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Drop steps | DROP_CHAIN_STEP |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
Alter steps | DEFINE_CHAIN_STEP |
Ownership of the chain, or ALTER privileges on the chain or
CREATE ANY JOB |
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; /
After creating a chain object, you define one or more chain steps. Each step can point to one of the following:
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.
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:
DEFINE_CHAIN_RULE
procedure and on Scheduler chain condition syntax.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:
Chains are automatically disabled by the Scheduler when: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:
_N
'
to the step name, where N
is an integer that results in a unique job subname.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:
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.
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.
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.
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:
Chains are automatically disabled by the Scheduler when: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.
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.
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.
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.
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.
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.
Note:
This chapter discusses the use of the Oracle-suppliedDBMS_SCHEDULER
package to administer scheduling capabilities. See
the Oracle Database PL/SQL Packages and Types
Reference for DBMS_SCHEDULER
syntax. An easier way to administer the Scheduler is with the graphical interface of Oracle
Enterprise Manager.
To administer the Scheduler with Enterprise Manager:
See Oracle Database 2 Day DBA for instructions.
The following tasks are necessary when configuring Oracle Scheduler (the Scheduler):
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.
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:
Task 2: Configuring the Scheduler Environment
This section discusses the following tasks:
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:
Oracle Database PL/SQL Packages and Types Reference forCREATE_JOB_CLASS
syntax, "Creating
Job Classes" for further information on job classes, and
"Examples of Creating Job Classes" for more
examples of creating job classesTask 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:
Oracle Database PL/SQL Packages and Types Reference forCREATE_WINDOW
syntax, "Creating
Windows" for further information on windows, and
"Examples of Creating Windows" for more examples
of creating job classesTask 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:
"Allocating Resources Among Jobs" for further information on resource plansTask 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:
Oracle Database PL/SQL Packages and Types Reference forCREATE_WINDOW_GROUP
syntax, "Using
Window Groups" for further information on window groups, and
"Example of Creating Window Groups" for more
detailed examples of creating window groupsTask 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.
The following sections discuss how to monitor and manage the Scheduler:
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;
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
Job State | Description |
---|---|
disabled |
The job is disabled. |
scheduled |
The job is scheduled to be executed. |
running |
The job is currently running. |
completed |
The job has completed, and is not scheduled to run again. |
stopped |
The job was scheduled to run once and was stopped while it was running. |
broken |
The job is broken. |
failed |
The job was scheduled to run once and failed. |
retry scheduled |
The job has failed at least once and a retry has been scheduled to be executed. |
succeeded |
The job was scheduled to run once and completed successfully. |
chain_stalled |
The job is of type chain and has no steps running, no steps scheduled to run, and
no event steps waiting on an event, and the chain evaluation_interval is set to NULL . No progress
will be made in the chain unless there is manual intervention. |
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:
Oracle Database Reference for details regarding the*_SCHEDULER_RUNNING_JOBS
and DBA_SCHEDULER_JOBS
viewsLogs 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:
Oracle Database Reference for details on the*_SCHEDULER_JOB_LOG
views and other Scheduler log views.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.
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.
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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB_CLASS
and SET_ATTRIBUTE
procedures and
"Task 2E: Setting Scheduler Attributes"A window log has an entry for each time you do the following:
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.
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');
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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theSET_ATTRIBUTE
procedureYou 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.
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:
When upgrading from Oracle Database 10g Release 1 to 10g Release 2 or later,CREATE EXTERNAL JOB
is automatically granted
to all users and roles that have the CREATE JOB
privilege. Oracle recommends that you revoke this privilege from users
that don't need it.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:
This section also contains the following topics:
See Also:
"About Remote External Jobs"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:
SYS
user.See "Connecting to the Database with SQL*Plus" for instructions.
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.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.
prvtrsch.plb
with following command:SQL> @?/rdbms/admin/prvtrsch.plb
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:
TheMANAGE 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.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:
setup.exe
./directory_path/runInstaller
where directory_path
is the path to the Oracle Database Gateway installation media.
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.
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.
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.
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".
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 withOracleSchedulerExecutionAgent
. 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 prevents the host on which it resides from running remote external jobs.
To stop the Scheduler agent:
AGENT_HOME/bin/schagent -stop
OracleSchedulerExecutionAgent
.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:
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.
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:
Oracle Database Utilities for details on Data PumpThis section contains the following troubleshooting topics:
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:
See Also:
"Job Recovery After a Failure"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.
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.
A job can become disabled for the following reasons:
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
.)
The Scheduler attempts to recover jobs that are interrupted when:
extjob
on Unix. On Windows, it is the external job service.)Job recovery proceeds as follows:
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"
restartable
is set to TRUE
for the job, the job is restarted.restartable
is set to FALSE
for the job:auto_drop
is set to TRUE
, the job run is done and the job is
dropped.auto_drop
is set to FALSE
, the job is disabled and the job
state
is set to 'STOPPED
'.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
'.
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.
A window can fail to take effect for the following reasons:
See "Using Windows" for more information regarding windows.
This section discusses the following topics:
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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB
procedure and
"Creating Jobs" for further informationThis 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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB_CLASS
procedure and
"Creating Job Classes" for further informationThis 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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_PROGRAM
procedure and
"Creating Programs" for further informationThis 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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_WINDOW
procedure and
"Creating Windows" for further informationThis 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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_WINDOW_GROUP
and ADD_WINDOW_GROUP_MEMBER
procedures and
"Creating Window Groups" for further informationThis 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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theSET_SCHEDULER_ATTRIBUTE
procedure and
"Task 2E: Setting Scheduler Attributes"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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_CHAIN
, DEFINE_CHAIN_STEP
, and DEFINE_CHAIN_RULE
procedures
and "Task 2E: Setting Scheduler Attributes"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:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB
and CREATE_EVENT_SCHEDULE
proceduresIn 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:
database_role
attribute of the new job to the required roleThe 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:
For a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes.This section contains reference information for Oracle Scheduler. It contains the following topics:
Table 28-2 lists the various Scheduler privileges.
Table 28-2 Scheduler Privileges
Privilege Name | Operations Authorized |
---|---|
System Privileges: | |
CREATE JOB |
This privilege enables you to create jobs, chains, schedules, programs, and credentials
in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do
not have the CREATE JOB privilege. In this case, the job must have been created in your schema by
another user with the CREATE ANY JOB privilege. |
CREATE ANY JOB |
This privilege enables you to create, alter, and drop jobs, chains, schedules,
programs, and regular credentials in any schema except SYS . This privilege is very powerful and should be used
with care because it allows the grantee to execute code as any other user. |
CREATE EXTERNAL JOB |
This privilege is required to create jobs that run outside of the database. Owners
of jobs of type 'EXECUTABLE ' or jobs that point to programs of type 'EXECUTABLE ' require this privilege.
To run a job of type 'EXECUTABLE ', you must have this privilege and the CREATE JOB privilege.
This privilege is also required to retrieve files from a remote host and to save files to one or more remote hosts. |
EXECUTE ANY PROGRAM |
This privilege enables your jobs to use programs or chains from any schema. |
EXECUTE ANY CLASS |
This privilege enables your jobs to run under any job class. |
MANAGE SCHEDULER |
This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes, purge Scheduler logs, drop public credentials, set the agent password for a database. |
Object Privileges: | |
EXECUTE |
This privilege can only be granted for programs, chains, and job classes. It enables you to create a job that runs with the program, chain, or job class. It also enables you to view object attributes. |
ALTER |
This privilege enables you to alter or drop the object it is granted on. Altering
includes such operations as enabling, disabling, defining or dropping program arguments, setting or resetting job argument values
and running a job. For programs, jobs, and chains, this privilege enables you to view object attributes. This privilege can
only be granted on jobs, chains, programs and schedules. For other types of Scheduler objects, you can grant the MANAGE
SCHEDULER system privilege. This privilege can be granted for:jobs ( chains ( programs ( schedules ( |
ALL |
This privilege authorizes operations allowed by all other object privileges possible for a given object. It can be granted on jobs, programs, chains, schedules and job classes. |
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
.
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:
In the following table, the asterisk at the beginning of a view name can be replaced withDBA
, ALL
, or
USER
.Table 28-3 Scheduler Views
View | Description |
---|---|
*_SCHEDULER_SCHEDULES |
These views show all schedules. |
*_SCHEDULER_PROGRAMS |
These views show all programs. |
*_SCHEDULER_PROGRAM_ARGS |
These views show all arguments defined for all programs as well as the default values if they exist. |
*_SCHEDULER_JOBS |
These views show all jobs, enabled as well as disabled. |
*_SCHEDULER_RUNNING_CHAINS |
These views show all chains that are running. |
*_SCHEDULER_CHAIN_STEPS |
These views show all steps for all chains. |
*_SCHEDULER_CHAINS |
These views show all chains. |
*_SCHEDULER_CHAIN_RULES |
These views show all rules for all chains. |
*_SCHEDULER_GLOBAL_ATTRIBUTE |
These views show the current values of Scheduler attributes. |
*_SCHEDULER_JOB_ARGS |
These views show all set argument values for all jobs. |
*_SCHEDULER_JOB_CLASSES |
These views show all job classes. |
*_SCHEDULER_WINDOWS |
These views show all windows. |
*_SCHEDULER_JOB_RUN_DETAILS |
These views show all completed (failed or successful) job runs. |
*_SCHEDULER_WINDOW_GROUPS |
These views show all window groups. |
*_SCHEDULER_WINGROUP_MEMBERS |
These views show the members of all window groups, one row for each group member. |
*_SCHEDULER_RUNNING_JOBS |
These views show state information on all jobs that are currently being run. |
*_SCHEDULER_JOB_LOG |
These views show all state changes made to jobs. |
*_SCHEDULER_WINDOW_LOG |
These views show all state changes made to windows. |
*_SCHEDULER_WINDOW_DETAILS |
These views show all completed window runs. |
*_SCHEDULER_CREDENTIALS |
These views show all credentials. |
*_SCHEDULER_JOB_ROLES |
These views show all jobs by Oracle Data Guard database role. |
By Jim CzuprynskiSynopsis. 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 FlowTo 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
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.
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
ORACLE-BASE - Scheduler in Oracle Database 10g
Database journal
|
Oracle 10g Scheduler Enhancements, Part 2 Job Chains - DatabaseJournal.com
Oracle Scheduler Training
Oracle Scheduler Utilities
In this chapter:
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.
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
Copyright © 1996-2021 by Softpanorama Society. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...
|
You can use PayPal to to buy a cup of coffee for authors of this site |
Disclaimer:
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