| Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-02 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-02 |
|
|
View PDF |
Oracle Database provides database job capabilities through Oracle Scheduler (the Scheduler). This chapter explains how to use the various Scheduler components, and discusses the following topics:
Note:
This chapter describes how to use the
DBMS_SCHEDULER package to work with Scheduler components. You can accomplish the same tasks using Oracle Enterprise Manager.Each Scheduler object is a complete database schema object of the form [schema.]name. Scheduler objects exactly follow the naming rules for database objects and share the SQL namespace with other database objects.
When names for Scheduler objects are used in the DBMS_SCHEDULER package, SQL naming rules continue to be followed. By default, Scheduler object names are uppercase unless they are surrounded by double quotes. For example, when creating a job, job_name => 'my_job' is the same as job_name => 'My_Job' and job_name => 'MY_JOB', but not the same as job_name => '"my_job"'. These naming rules are also followed in those cases where comma-delimited lists of Scheduler object names are used within the DBMS_SCHEDULER package.
See Oracle Database SQL Reference for details regarding naming objects.
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:
See Also:
"Jobs" for an overview of jobs.Table 27-1 illustrates common job tasks and their appropriate procedures and privileges:
Table 27-1 Job Tasks and Their Procedures
| Task | Procedure | Privilege Needed |
|---|---|---|
|
Create a job |
|
|
|
Alter a job |
|
|
|
Run a job |
|
|
|
Copy a job |
|
|
|
Drop a job |
|
|
|
Stop a job |
|
|
|
Disable a job |
|
|
|
Enable a job |
|
|
See "How to Manage Scheduler Privileges" for further information regarding privileges.
You create jobs using the CREATE_JOB procedure or Enterprise Manager. When creating a job, you specify an action, a schedule, and other attributes. For example, the following statement creates a 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 procedure or Enterprise Manager. (Some job attributes can be set only with the SET_ATTRIBUTE procedure or Enterprise Manager.)
See Oracle Database PL/SQL Packages and Types Reference for information about the SET_ATTRIBUTE procedure and about the various job attributes.
After creating a job, you may need to set job arguments if:
The inline job action is a stored procedure or other executable that requires arguments
The job references a named program object and you want to override one or more default program arguments
The job references a named program object and one or more of the program arguments were not assigned a default value
To set job arguments, use the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures or Enterprise Manager. SET_JOB_ANYDATA_VALUE is used for complex data types that must be encapsulated in an ANYDATA object.
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.
You create a job using the CREATE_JOB procedure or Enterprise Manager. Because this 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 also 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 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; /
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 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 new 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; /
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 procedure 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 SET_ATTRIBUTE call 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 using the SET_ATTRIBUTE procedure, 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 procedure and "Configuring the 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.
An external job is a job that runs outside the database. All external jobs run as a low-privileged guest user, as has been determined by the database administrator while configuring external job support. Because the executable will be run as a low-privileged guest account, you should verify that it has access to necessary files and resources. Most, but not all, platforms support external jobs. For platforms that do not support external jobs, creating or setting the attribute of a job or a program to type EXECUTABLE returns an error. See your operating system-specific documentation for more information.
For an external job, job_type is specified as EXECUTABLE (If using named programs, the corresponding program_type would be EXECUTABLE). job_action (or corresponding program_action if using named programs) is the full OS-dependent path of the desired external executable plus optionally any command line arguments. For example, /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for type EXECUTABLE must be a string type such as CHAR, VARCHAR2, or VARCHAR.
Some additional post-installation steps might be required to ensure that external jobs run as a low-privileged guest user. See your operating system-specific documentation for any post-installation configuration steps.
Note:
The owner of an external job must have theCREATE EXTERNAL JOB system privilege before the job can be enabled or run.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.
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 external job 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 external job 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 a 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.
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 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 calling STOP_JOB with the force option set to FALSE), and then drops the job. By default, force is set to FALSE.
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;
/
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.
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.
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 |
|
|
|
Alter a program |
|
|
|
Drop a program |
|
|
|
Disable a program |
|
|
|
Enable a program |
|
|
See "How to Manage 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:
From the Administration page, click Programs
The Scheduler Programs page appears. It displays existing programs.
Click in the Select column to select a program, and then click Edit.
The Edit Program page appears.
Next to the Enabled heading, select Yes or No.
In the Description field, change any comments.
From the Type drop-down list, select one of the following:
PLSQL_BLOCK
A Source field appears. Enter or alter the PL/SQL code in this field.
STORED_PROCEDURE
A Procedure Name field appears. If the field contains a stored procedure name, click View Procedure to view or edit the stored procedure. If the field is blank, or if you want to change stored procedures, click Select Procedure. A Select Procedure page then appears. Select a stored procedure and then click Select to return to the Edit Program page. (Click Help at the top of the page for help with using the Select Procedure page.)
With a procedure name selected, a list of arguments appears under the Arguments heading on the Edit Program page. Optionally enter default values for one or more arguments.
EXECUTABLE
An Executable Name field appears. Enter the full path of the executable. Under the Arguments heading, edit or delete arguments, or click Add Another Row to add an argument.
Click Apply to save your changes.
If any currently running jobs use the program that you altered, they continue to run with the program as defined before the alter operation.
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 |
|
|
|
Alter a schedule |
|
|
|
Drop a schedule |
|
|
See "How to Manage 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:
Start date
Using the calendaring syntax, the start date is a reference date only. This means that the schedule is valid as of this date. It does not mean that the job will start on the start date.
Using a PL/SQL expression, the start date represents the actual time that the job will start executing for the first time.
Next run time
Using the calendaring syntax, the next time the job will run is fixed.
Using the PL/SQL expression, the next time the job will run depends on the actual start time of the current run of the job. As an example of the difference, if a job started at 2:00 PM and its schedule was to repeat every 2 hours, then, if the repeat interval was specified with the calendaring syntax, it would repeat at 4, 6 and so on. If PL/SQL was used and the job started at 2:10, then the job would repeat at 4:10, and if the next job actually started at 4:11, then the subsequent run would be at 6:11.
To illustrate these two points, consider a situation where you have a start date of 15-July-2003 1:45:00 and you want it to repeat every two hours. A calendar expression of "FREQ=HOURLY; INTERVAL=2; BYMINUTE=0;" will generate the following schedule:
TUE 15-JUL-2003 03:00:00 TUE 15-JUL-2003 05:00:00 TUE 15-JUL-2003 07:00:00 TUE 15-JUL-2003 09:00:00 TUE 15-JUL-2003 11:00:00 ...
Note that the calendar expression repeats every two hours on the hour.
A PL/SQL expression of "SYSTIMESTAMP + interval '2' hour", however, might have a run time of the following:
TUE 15-JUL-2003 01:45:00 TUE 15-JUL-2003 03:45:05 TUE 15-JUL-2003 05:45:09 TUE 15-JUL-2003 07:45:14 TUE 15-JUL-2003 09:45:20 ...
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 |
|
|
|
Alter a job class |
|
|
|
Drop a job class |
|
|
See "How to Manage 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 the 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 the 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:
Schedule
This controls when the window is in effect.
Duration
This controls how long the window is open.
Resource plan
This names the resource plan that activates when the window opens.
Only one window can be in effect at any given time. Windows belong to the SYS schema.
All window activity is logged in the *_SCHEDULER_WINDOW_LOG views, otherwise known as the window logs. See "Window Logs" for examples of window logging.
This section introduces you to basic window tasks, and discusses the following topics:
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 |
|
|
|
Open a window |
|
|
|
Close a window |
|
|
|
Alter a window |
|
|
|
Drop a window |
|
|
|
Disable a window |
|
|
|
Enable a window |
|
|
See "How to Manage 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 the Scheduler" for more information.
The following are instructions for creating a window with Enterprise Manager:
From the Administration page, click Windows under the Database Scheduler heading.
The Scheduler Windows page appears. It displays existing windows.
Click Create.
The Create Window page appears.
Enter a name for the window.
Choose a resource plan from the Resource Plan drop-down list, or create a resource plan.
You can use the default, INTERNAL_PLAN. To view the contents of an existing resource plan, click View Resource Plan. If you choose to create a new resource plan, click Create Resource Plan and follow those steps.
Select a priority, Low or High.
Enter optional comments in the Description field.
Under the Schedule heading, do one of the following:
To create a schedule, select Use a calendar.
To use an existing schedule, select Use an existing schedule. A schedule with its information is displayed. If you want a different schedule, click Select Schedule, in which case the Select Schedule page is displayed. Select one of the schedules listed under the Results heading, or enter the schema and a text string for the schedule name, and then click Go. The schedule with the search string in its name is returned. Select the desired schedule and click Select.
Note:
The Scheduler does not check if there is already a window defined for that schedule. Therefore, this may result in windows that overlap. Also, using a named schedule that has a PL/SQL expression as its repeat interval is not supported for windows.Click OK, and skip the remaining steps in this procedure.
If you want to change the time zone, click the flashlight icon next to the Time Zone field and follow the steps.
Under the Repeating drop-down list, choose how often you want the window to repeat. If you choose a value other than Do Not Repeat, the page changes so that you can enter the interval and enter a starting time.
Under the Available to Start heading, select whether you want the schedule to start Immediately or Later. If you choose Later, enter a date.
Under the Duration heading, enter how long the window is to remain open.
Under the Not Available After heading, optionally specify an end date.
Click OK to save the window.
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 the 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:
According to the window's schedule
Manually, using the OPEN_WINDOW procedure
This procedure opens the window independent of its schedule. This window will open and the resource plan associated with it will take effect immediately. Only an enabled window can be manually opened.
In the OPEN_WINDOW procedure, you can specify the time interval that the window should be open for, using the duration attribute. The duration is of type interval day to second. If the duration is not specified, then the window will be opened for the regular duration as stored with the window.
Opening a window manually has no impact on regular scheduled runs of the window.
When a window that was manually opened closes, the rules about overlapping windows are applied to determine which other window should be opened at that time if any at all.
You can force a window to open even if there is one already open by setting the force option to TRUE in the OPEN_WINDOW call or Enterprise Manager.
When the force option is set to TRUE, the Scheduler automatically closes any window that is open at that time, even if it has a higher priority. For the duration of this manually opened window, the Scheduler does not open any other scheduled windows even if they have a higher priority. You can open a window that is already open. In this case, the window stays open for the duration specified in the call, from the time the OPEN_WINDOW command was issued.
Consider an example to illustrate this. window1 was created with a duration of four hours. It has how been open for two hours. If at this point you reopen window1 using the OPEN_WINDOW call and do not specify a duration, then window1 will be open for another four hours because it was created with that duration. If you specified a duration of 30 minutes, the window will close in 30 minutes.
When a window opens, an entry is made in the window log.
A window can fail to switch resource plans if the current resource plan has been manually switched using the ALTER SYSTEM statement with the FORCE option, or using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure with the allow_scheduler_plan_switches argument set to FALSE. In this case, the failure to switch resource plans is written to the window log.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the OPEN_WINDOW procedure and the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure.
There are two ways a window can close:
Based on a schedule
A window will close based on the schedule defined at creation time.
Manually, using the CLOSE_WINDOW procedure
The CLOSE_WINDOW procedure will close an open window prematurely.
A closed window means that it is no longer in effect. When a window is closed, the Scheduler will switch the resource plan to the one that was in effect outside the window or in the case of overlapping windows to another window. If you try to close a window that does not exist or is not open, an error is generated.
A job that is running will not close when the window it is running in closes unless the attribute stop_on_window_close was set to TRUE when the job was created. However, the resources allocated to the job may change because the resource plan may change.
When a running job has a window group as its schedule, the job will not be stopped when its window is closed if another window that is also a member of the same window group then becomes active. This is the case even if the job was created with the attribute stop_on_window_close set to TRUE.
When a window is closed, an entry will be added to the window log DBA_SCHEDULER_WINDOW_LOG.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the CLOSE_WINDOW procedure.
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:
If windows of the same priority overlap, the window that is active will stay open. However, if the overlap is with a window of higher priority, the lower priority window will close and the window with the higher priority will open. Jobs currently running that had a schedule naming the low priority window may be stopped depending on the behavior you assigned when you created the job.
If at the end of a window there are multiple windows defined, the window with the highest priority will open. If all windows have the same priority, the window that has the highest percentage of time remaining will open.
An open window that is dropped will be automatically closed. At that point, the previous rule applies.
Whenever two windows overlap, an entry is written in the Scheduler log.
Figure 27-1 illustrates a typical example of how windows, resource plans, and priorities might be determined for a 24 hour schedule. In the following two examples, assume that Window1 has been associated with Resource Plan1, Window2 with Resource Plan2, and so on.
Figure 27-1 Windows and Resource Plans (Example 1)
In Figure 27-1, the following occurs:
From 12AM to 4AM
No windows are open, so a default resource plan is in effect.
From 4AM to 6AM
Window1 has been assigned a low priority, but it opens because there are no high priority windows. Therefore, Resource Plan 1 is in effect.
From 6AM to 9AM
Window3 will open because it has a higher priority than Window1, so Resource Plan 3 is in effect.
From 9AM to 11AM
Even though Window1 was closed at 6AM because of a higher priority window opening, at 9AM, this higher priority window is closed and Window1 still has two hours remaining on its original schedule. It will be reopened for these remaining two hours and resource plan will be in effect.
From 11AM to 2PM
A default resource plan is in effect because no windows are open.
From 2PM to 3PM
Window2 will open so Resource Plan 2 is in effect.
From 3PM to 8PM
Window4 is of the same priority as Window2, so it will not interrupt Window2. Therefore, Resource Plan 2 is in effect.
From 8PM to 10PM
Window4 will open so Resource Plan 4 is in effect.
From 10PM to 12AM
A default resource plan is in effect because no windows are open.
Figure 27-2 illustrates another example of how windows, resource plans, and priorities might be determined for a 24 hour schedule.
Figure 27-2 Windows and Resource Plans (Example 2)
In Figure 27-2, the following occurs:
From 12AM to 4AM
A default resource plan is in effect.
From 4AM to 6AM
Window1 has been assigned a low priority, but it opens because there are no high priority windows, so Resource Plan 1 is in effect.
From 6AM to 9AM
Window3 will open because it has a higher priority than Window1. Note that Window6 does not open because another high priority window is already in effect.
From 9AM to 11AM
At 9AM, Window5 or Window1 are the two possibilities. They both have low priorities, so the choice is made based on which has a greater percentage of its duration remaining. Window5 has a larger percentage of time remaining compared to the total duration than Window1. Even if Window1 were to extend to, say, 11:30AM, Window5 would have 2/3 * 100% of its duration remaining, while Window1 would have only 2.5/7 * 100%, which is smaller. Thus, Resource Plan 5 will be in effect.
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 |
|
|
|
Drop a window group |
|
|
|
Add a member to a window group |
|
|
|
Drop a member to a window group |
|
|
|
Enable a window group |
|
|
|
Disable a window group |
|
|
See "How to Manage 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 Types Reference for detailed information about the DISABLE procedure.
The Scheduler works with two kinds of events:
Events that the Scheduler raises to notify applications of a change in job state (for example, job complete)
Events that applications raise to notify the Scheduler to start a job
This section provides details on how to work with both kinds of events, and includes the following topics:
See Also:
"Events" for an overview of Scheduler events
"Using Chains" for information on how to use events with chains to achieve precise control over process flow
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 |
|
|
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. For a list of bit flags, see the discussion of DBMS_SCHEDULER.SET_ATTRIBUTE in Oracle Database PL/SQL Packages and Types Reference.
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. (Event types are defined as constants in the package.)
job_failed (bit flag value = 4)
job_sch_lim_reached (bit flag value = 64)
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('dw_reports', 'raise_events',
DBMS_SCHEDULER.JOB_FAILED + DBMS_SCHEDULER.JOB_SCH_LIM_REACHED);
END;
/
To consume Scheduler events, your application must subscribe to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE. This queue is a secure queue and is owned by SYS. To create a subscription to this queue for a user, do the following:
Log in to the database as the SYS user or as a user with the MANAGE ANY QUEUE privilege.
Subscribe to the queue using a new or existing agent.
Run the package procedure DBMS_AQADM.ENABLE_DB_ACCESS as follows:
DBMS_AQADM.ENABLE_DB_ACCESS(agent_name, db_username);
where agent_name references the agent that you used to subscribe to the events queue, and db_username is the user for whom you want to create a subscription.
There is no need to grant dequeue privileges to the user. The dequeue privilege is granted on the Scheduler event queue to PUBLIC.
As an alternative, the user can subscribe to the Scheduler event queue using the ADD_EVENT_QUEUE_SUBSCRIBER procedure, as shown in the following example:
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER(subscriber_name);
where subscriber_name is the name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. (If it is NULL, an agent is created whose name is the user name of the calling user.) This call both creates a subscription to the Scheduler event queue and grants the user permission to dequeue using the designated agent. The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. After the subscription is in place, the user can either poll for messages at regular intervals or register with AQ for notification.
See Oracle Streams Advanced Queuing User's Guide and Reference 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 |
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 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
See Also:
Oracle Streams Advanced Queuing User's Guide and Reference 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:
The job must use a named program of type STORED_PROCEDURE.
One of the named program's arguments must be a metadata argument with metadata_attribute set to EVENT_MESSAGE.
The stored procedure that implements the program must have an argument at the position corresponding to the named program's metadata argument. The argument type must be the data type of the queue where your application queues the job-start event.
If you use the RUN_JOB procedure to manually run a job that has an EVENT_MESSAGE metadata argument, the value passed to that argument is NULL.
The following example shows how to construct an event-based job that can receive the event message content:
create or replace procedure my_stored_proc (event_msg IN event_queue_type)
as
begin
-- retrieve and process message body
-- do other work
end;
/
begin
dbms_scheduler.create_program (
program_name => 'my_prog',
program_action=> 'my_stored_proc',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => FALSE) ;
dbms_scheduler.define_metadata_argument (
program_name => 'my_prog',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
dbms_scheduler.enable ('my_prog');
exception
when others then raise ;
end ;
/
begin
dbms_scheduler.create_job (
job_name => 'my_evt_job' ,
program_name => 'my_prog',
schedule_name => 'my_evt_sch',
enabled => true,
auto_Drop => false) ;
exception
when others then raise ;
end ;
/
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:
"Chains" for an overview of chains
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 |
|
|
|
Drop a chain |
|
Ownership of the chain or |
|
Alter a chain |
|
Ownership of the chain, or |
|
Alter a chain |
|
Ownership of the chain, or |
|
Alter a running chain |
|
Ownership of the job, or |
|
Run a chain |
|
|
|
Add rules to a chain |
|
Ownership of the chain, or |
|
Alter rules in a chain |
|
Ownership of the chain, or |
|
Drop rules from a chain |
|
Ownership of the chain, or |
|
Enable a chain |
|
Ownership of the chain, or |
|
Disable a chain |
|
Ownership of the chain, or |
|
Create steps |
|
Ownership of the chain, or |
|
Drop steps |
|
Ownership of the chain, or |
|
Alter steps |
|
Ownership of the chain, or |
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:
A program
Another chain (a nested chain)
An event
You define a step that points to a program or nested chain by using the DEFINE_CHAIN_STEP procedure. An example is the following, which adds two steps to my_chain1:
BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'my_chain1', step_name => 'my_step1', program_name => 'my_program1'); DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'my_chain1', step_name => 'my_step2', program_name => 'my_chain2'); END; /
To define a step that waits for an event to occur, you use the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule or can include an inline queue specification and event condition. This example creates a third chain step that waits for the event specified in the named event schedule:
BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP ( chain_name => 'my_chain1', step_name => 'my_step3', event_schedule_name => 'my_event_schedule'); END; /
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DEFINE_CHAIN_STEP and DEFINE_CHAIN_EVENT_STEP procedures.
Chain rules define when steps run, and define dependencies between steps. Each rule has a condition and an action. If the condition evaluates to TRUE, the action is performed. The condition can contain Scheduler chain condition syntax or any syntax that is valid in a SQL WHERE clause. The syntax can include references to attributes of any chain step, including step completion status. A typical action is to run a specified step.
Conditions are usually based on the outcome of one or more previous steps. For example, you might want one step to run if the two previous steps succeeded, and another to run if either of the two previous steps failed.
All rules added to a chain work together to define the overall behavior of the chain. When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. (You can cause rules to be evaluated at regular intervals also. See "Creating Chains" for details.)
You add a rule to a chain with the DEFINE_CHAIN_RULE procedure. You call this procedure once for each rule that you want to add to the chain.
Starting the Chain
At least one rule must have a condition that always evaluates to TRUE so that the chain can start when the job starts. The easiest way to accomplish this is to just set the condition to 'TRUE' if you are using Schedule chain condition syntax, or '1=1' if you are using SQL syntax.
Ending the Chain
At least one chain rule must contain an action of 'END'. A chain job does not complete until one of the rules containing the END action evaluates to TRUE. Several different rules with different END actions are common, some with error codes, and some without.
If a chain has no more running steps or it is not waiting for an event to occur, and no rules containing the END action evaluate to TRUE (or there are no rules with the END action), the job enters the CHAIN_STALLED state. See "Handling Stalled Chains" for more information.
Example
The following example defines a rule that starts the chain at step 1 and a rule that starts step 2 when step 1 completes. rule_name and comments are optional and default to NULL.
BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( chain_name => 'my_chain1', condition => 'TRUE', action => 'START step1', rule_name => 'my_rule1', comments => 'start the chain'); DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( chain_name => 'my_chain1', condition => 'step1 completed', action => 'START step2', rule_name => 'my_rule2'); END; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for information on the DEFINE_CHAIN_RULE procedure and on Scheduler chain condition syntax.
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:The program that one of the chain steps points to is dropped
The nested chain that one of the chain steps points to is dropped
The event schedule that one of the chain event steps points to is dropped
To run a chain, you must either use the RUN_CHAIN procedure or create a job of type 'CHAIN'. The job action must refer to the chain name, as shown in the following example:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'chain_job_1', job_type => 'CHAIN', job_action => 'my_chain1', repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0', enabled => TRUE); END; /
For every step of a chain job that is running, the Scheduler creates a step job with the same job name and owner as the chain job. Each step job additionally has a job subname to uniquely identify it. The job subname is included as a column in the views *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, and *_SCHEDULER_JOB_RUN_DETAILS. The job subname is normally the same as the step name except in the following cases:
For nested chains, the current step name may have already been used as a job subname. In this case, the Scheduler appends '_N' to the step name, where N is an integer that results in a unique job subname.
If there is a failure when creating a step job, the Scheduler logs a FAILED entry in the job log views (*_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS) with the job subname set to 'step_name_0'.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information on the CREATE_JOB procedure.
"Running Chains" for another way to run a chain without creating a job ahead of time.
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:The program that one of the chain steps points to is dropped
The nested chain that one of the chain steps points to is dropped
The event schedule that one of the chain event steps points to is dropped
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.
Resource Manager is the database feature that controls how resources are allocated in the database. 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 will be allocated among the various groups. See Chapter 24, "Using the Database Resource Manager" for more information about what resources are controlled by 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 will map to the default consumer group.
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 Resource Manager are tightly integrated. The job coordinator obtains database resource availability from 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 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, Resource Manager will continue to manage the amount of CPU cycles that are assigned to each running job based on the specified resource plan. Keep in mind that Resource Manager can only manage database processes. The active management of CPU cycles does not apply to jobs of type executable.
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.
In a Real Application Clusters environment, the same resource plan will be in effect on every database instance.
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.
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.
