| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
|
View PDF |
Oracle Database provides database scheduling capabilities through the Scheduler. This chapter introduces you to its use, and discusses the following topics:
Each Scheduler object is a complete database schema object of the form [schema.]name. Scheduler objects exactly follow the naming rules for database objects, so they must be unique in the SQL namespace.
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, its name must be provided. 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:
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 | CREATE_JOB |
CREATE JOB or CREATE ANY JOB |
| Alter a job | SET_ATTRIBUTE |
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 "How to Manage Scheduler Privileges" for further information regarding privileges.
You create jobs using the CREATE_JOB procedure. When creating a job, you must specify the action of the job, the schedule for the job, as well as some other attributes of the job. For example, the following statement creates a job called my_emp_job1, which is an insert into the sales table:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'my_emp_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'INSERT INTO sales VALUES( 7987, ''SALLY'',
''ANALYST'', NULL, NULL, NULL, NULL, NULL);',
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',
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.
Once a job is created, it can be queried using the *_SCHEDULER_JOBS views. Jobs are created disabled by default and they need to be enabled in order to be executed.
Some job attributes are set at job creation time, while other job attributes are not. Instead, you can specify these attributes after the job has been created by using the SET_ATTRIBUTE procedure. See PL/SQL Packages and Types Reference for information about the SET_ATTRIBUTE procedure.
After creating a job, you may need to set job arguments. To set job arguments, use the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures. Both procedures have the same purpose, but SET_JOB_ANYDATA_VALUE is used for types that cannot be implicitly converted to and from VARCHAR2. A typical situation where you might want to set a job argument is for adding a new employee to a department. In this case, you might have a job that adds employees and assigns them the next available number in the department for a department ID. The following statement does this:
BEGIN DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ( job_name => 'my_emp_job1', argument_position => 2, argument_value => 'John_Newman'); END; /
If you use this procedure on an argument whose value has already been set, it will be overwritten. You can set argument values either by using the argument name or by the argument position. 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, us the RESET_JOB_ARGUMENT procedure. This procedure can be used for both regular and anydata arguments.
See PL/SQL Packages and Types Reference for information about the procedures used in setting job arguments and their syntax.
You create a job using the CREATE_JOB procedure. 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 saved program and schedule. This is discussed in the following sections:
You can also create a job by pointing to a saved program instead of inlining its action. To create a job using a saved 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 saved 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 saved schedule instead of inlining its schedule. To create a job using a saved 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 saved schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a saved 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 foo(); END;', schedule_name => 'my_saved_schedule'); END; /
A job can also be created by pointing to both a saved program and schedule. An example of using the CREATE_JOB procedure with a saved 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. 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.
You alter a job using the SET_ATTRIBUTE procedure. All jobs can be altered, and, with the exception of the job name, all job attributes can be changed. If an enabled job is altered, the Scheduler will disable it, make the change and then reenable it. If any errors are encountered during the enable process, the job is not enabled and an error is generated. 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.
If any of the schedule attributes of a job are altered while the job is running, the next job run will be scheduled using the new schedule attributes. Schedule attributes of a job include schedule_name, start_date, end_date, and repeat_interval.
If any of the program attributes of a job are altered while the job is running, the new program attributes will take effect the next time the job runs. Program attributes of a job include program_name, job_action, job_type, and number_of_arguments. This is also the case for job argument values that have been set.
Granting ALTER on a job will let a user alter all attributes of that job except its program attributes (program_name, job_type, job_action, program_action, and number_of_arguments) and will not allow a user to use a PL/SQL expression to specify the schedule for a 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 several views. The attributes of a job are available in the *_SCHEDULER_JOB 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 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 execute 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.
Once 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. To run the job using the RUN_JOB procedure, it must be enabled.
You can use the RUN_JOB procedure to test a job, thereby ensuring that it runs without errors. It can also be used to run a job outside of its specified schedule. For example, if an instance of a job failed because of some error. Once you fix the errors, you can use this procedure to run the job instead of scheduling a separate job for it.
Running a job using the RUN_JOB procedure with its 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.
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 are 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.
To ensure that environment variables can be used with external jobs, you can use a wrapper such as Perl or sh before invoking the external job. As an example, if you have an external job (hello.exe) with USER_NAME and LOCATION environment variables, you could create a perl wrapper (hello.pl) such as the following:
$ENV { "USER_NAME" } = $ARGV[1];
$ENV { "LOCATION" } = $ARGV[2];
system ($ARGV[0]);
With a program_action of C:\home\hello.exe, the values would be C:\perl\bin\perl.exe (or /usr/local/bin/perl on UNIX, or /home/mydir/bin/hello.pl, if hello.pl is executable). Your program arguments would be the following:
"C:\home\hello.exe" --- the path where hello.exe is located "Myname" --- the value for USER_NAME "Mytown" --- the value for LOCATION
You stop a running job using the STOP_JOB procedure. Job classes reside in the SYS schema, therefore, whenever job classes are used in comma-delimited lists, they must be preceded by SYS. For example, the following statement stops job1:
BEGIN
DBMS_SCHEDULER.STOP_JOB('job1');
END;
/
Any instance of the job will be stopped. After stopping the job, the state of a one-time job will be set to STOPPED whereas the state of a repeating job will be set to SCHEDULED because the next run of the job is scheduled.
The Scheduler tries to gracefully stop the job using an interrupt mechanism. This method gives control back to the slave process, which can update the status of the job to STOPPED.
If the interrupt is not successful, the STOP_JOB call will fail. The job will be stopped as soon as possible after its current uninterruptable operation is done. Users with the MANAGE SCHEDULER privilege can force the job to stop sooner by setting the force option to TRUE in the STOP_JOB call. In this case, the call forcibly terminates the slave process that was running the job, thus stopping the job.
The STOP_JOB procedure accepts job_name as an argument. This can be the name of a job or a comma-delimited list of job names. It can also be the name of a job class or a list of job class names. For example, the following statement combines both jobs and job classes:
BEGIN
DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3,
sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/
If the name of a job class is specified using STOP_JOB, the jobs that belong to that job class are stopped. The job class is not affected by this call.
Only running jobs can be stopped. Stopping a job that is not running generates a PL/SQL exception saying that the job is not running. Stopping a job that does not exist also causes an error. When a list of job names is provided, the Scheduler stops executing the list of jobs on the very first job that returns an error.
|
Caution: When a job is stopped, only the current transaction will be rolled back. Note that if there are commits in the executable that the job is running, then only the current transaction will be rolled back. This can cause data inconsistency. |
You drop a job using the DROP_JOB procedure. 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 attempts to first stop (issues the STOP_JOB call) the running job instance and then drop the job. By default, force is set to FALSE. If the user does not have privileges to stop the job, the DROP_JOB call will fail.
The DROP_JOB procedure accepts job_name as an argument. This can be the name of a job or a comma-delimited list of job names. It can also be the name of a job class or a list of job class names. For example, the following statement combines both jobs and job classes:
BEGIN
DBMS_SCHEDULER.DROP_JOB ('job1, job2, job3,
sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/
If the name of a job class is specified in this procedure call, the jobs that belong to that job class are dropped, but the job class itself is not dropped. The DROP_JOB_CLASS procedure should be used to drop the job class. See "Dropping Job Classes" for information about how to drop job classes.
Attempting to drop a job or job class that does not exist generates an error stating that the object does not exist. If a list of job names is specified in the DROP_JOB call, the call fails on the first job that cannot be dropped. In the preceding example, if job2 could not be dropped, the DROP_JOB call fails. job1 will be dropped but it will not be attempted to drop the other jobs in the list. The error returned by the Scheduler will contain the name of the job that caused the error.
You disable a job using the DISABLE procedure. 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 execute 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;
/
Note that if a list of job class names is provided, the jobs in the job class are disabled.
Note that if it is not possible to disable job2, then the DISABLE call will fail. job1 will be disabled but job2, job3, and jobs in jobclass1, and jobclass2 will not be disabled.
You enable jobs by using the ENABLE procedure. 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;
/
Note that if a list of job class names is provided, the jobs in the job class are enabled. Also, if it is not possible to enable job2, then the ENABLE call will fail. job1 will be enabled but job2, job3, jobclass1, and jobclass2 will not be enabled.
A program is a collection of metadata about a particular task. This section introduces you to basic program tasks, and discusses the following topics:
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 "How to Manage Scheduler Privileges" for further information regarding privileges.
You create programs by using the CREATE_PROGRAM procedure. 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; /
|
See Also: PL/SQL Packages and Types Reference for detailed information about theSET_ATTRIBUTE procedure and "Configuring the Scheduler" |
After creating a program, you will want to define program arguments when planning its execution. All arguments must be defined before the program can be enabled.
To set program argument values, use the DEFINE_PROGRAM_ARGUMENT or DEFINE_ANYDATA_ARGUMENT procedures. Both procedures have the same purpose, but DEFINE_ANYDATA_ARGUMENT is used for types that cannot be converted to VARCHAR2. A typical situation where you might want to define a program argument is for adding a new employee to a company. In this case, you might have a job that adds employees and assigns them the next available number in the company for an employee ID. The following statement does this:
BEGIN DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT ( program_name => 'my_program2', argument_position => 2, argument_name => 'ename', argument_type => 'VARCHAR2', default_value => 'N/A'); END; /
You can drop a program argument either by name or by position, as in the following statements:
BEGIN DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT ( program_name => 'my_program2', argument_position => 2); DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT ( program_name => 'my_program2', argument_name => 'ename'); 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. You can set the following arguments:
job_name
job_owner
job_start
window_start
window_end
|
See Also: PL/SQL Packages and Types Reference for detailed information about theSET_ATTRIBUTE and DEFINE_METADATA_ARGUMENT procedures and "Configuring the Scheduler" |
You alter programs by using the SET_ATTRIBUTE or SET_ATTRIBUTE_NULL procedure. With the exception of program name, all program attributes can be changed. If any currently running jobs use the program that is altered, they will continue to run with the program definition prior to the alter. The job will run with the new program definition the next time the job executes.
When a program is altered and it was in the enabled state, the Scheduler first disables it, applies the change, and then reenables it. If any errors are encountered during the enable process, the program is not reenabled and an error is generated.
The SET_ATTRIBUTE_NULL is only required for setting the value of any program attribute to NULL.
You drop a program using the DROP_PROGRAM procedure. You can also drop several programs in one call by providing a comma-delimited list of program names to the procedure. For example, the following statement drops all three programs:
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM('program1, program2, program3');
END;
/
Dropping a program that does not exist generates an error stating that the program does not exist. Note that if it is not possible to drop program2 in the preceding example, then the DROP_PROGRAM call fails. program1 will be dropped but program2 and program3 are not dropped.
If there are jobs that point to the program that you are trying to drop, you will not be able to drop the program unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the program is dropped, those jobs that point to the program will be disabled.
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 disable a program using the DISABLE procedure. 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.
You can also disable several programs in one call by providing a comma-delimited list of program names to the DISABLE procedure call. For example, the following statement disables all three programs:
DBMS_SCHEDULER.DISABLE('program1, program2, program3');
Disabling a program that does not exist generates an error stating that the program does not exist. Note that if it is not possible to disable program2 in this example, then the DISABLE call will fail. program1 will be disabled but program2 and program3 will not be disabled.
If there are jobs that point to the program that you are trying to disable, you will not be able to disable the program unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the program is disabled, those jobs that point to the program, will not be disabled, however, the job will fail at runtime because its program will not be valid.
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.
You enable a program using the ENABLE procedure. 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;
/
Enabling a program that does not exist will cause an error stating that the program does not exist. Note that if it is not possible to enable program2 in this example, then the ENABLE call will fail. program1 will be enabled but program2 and, program3 will not be enabled.
A schedule defines when a job should be run or when a window should open. Schedules can be saved. Schedules can be shared among users by creating and saving them as an object in the database.
This section introduces you to basic schedule tasks, and discusses the following topics:
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 "How to Manage Scheduler Privileges" for further information regarding privileges.
You create schedules by using the CREATE_SCHEDULE procedure. 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; /
You alter a schedule by using the SET_ATTRIBUTE procedure. Altering a schedule changes the definition of the schedule. With the exception of schedule name, all attributes can be changed. The attributes of a job 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.
You drop a schedule using the DROP_SCHEDULE procedure. This procedure call will delete the schedule object from the database. You can also drop several schedules in one call by providing a comma-delimited list of schedule names to the DROP_SCHEDULE procedure call. For example, the following statement drops three schedules:
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE('schedule1, schedule2, schedule3');
END;
/
If there are jobs or windows that use this schedule, the DROP_SCHEDULE call will fail unless you set force to TRUE. If you set force to TRUE, then the jobs and windows that use this schedule will be disabled before the schedule is dropped.
You control how often a job repeats by setting the repeat_interval attribute. The expression specified is evaluated to determine the next time the job should run. If no value for repeat_interval is specified, the job will run 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 Oracle Database calendaring expression. See PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure.
The following examples illustrate simple tasks.
Execute every Friday.
FREQ=WEEKLY; BYDAY=FRI;
Execute every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
Execute on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY-1;
Execute on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY-2;
Execute on March 10th.
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
Execute every 10 days.
FREQ=DAILY; INTERVAL=10;
Execute daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;
Execute on the 15th day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;
Execute on the 29th day of every month.
FREQ=MONTHLY; BYMONTHDAY=29;
Execute on the second Wednesday of each month.
FREQ=MONTHLY; BYDAY=2WED;
Execute on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;
Execute every 50 hours.
FREQ=HOURLY; INTERVAL=50;
Execute on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY-1;
Execute hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;
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 ...
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-03 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 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-03 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 calendering 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 execute is fixed.
Using the PL/SQL expression, the next time the job will execute 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 execute 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 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 can be difficult to manage on an individual basis, so the Scheduler uses job classes, which group jobs with common characteristics and behavior together. Prioritization among job classes is possible using resource plans.
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. Job classes are created in the SYS schema.
This section introduces you to basic job class tasks, and discusses the following topics:
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 "How to Manage Scheduler Privileges" for further information regarding privileges.
You create a job class using the CREATE_JOB_CLASS procedure. 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.
Job classes are created in the SYS schema. For users to create jobs that belong to a job class, the job owner must have EXECUTE privileges on the job class. Therefore, after the job class has been created, EXECUTE privileges must be granted on the job class so that users create jobs belonging to that class. You can also grant the EXECUTE privilege to a role.
See 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 can alter a job class by using the SET_ATTRIBUTE procedure. With the exception of the default job class, all job classes can be altered. 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 PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure and "Configuring the Scheduler".
You can drop a job class using the DROP_JOB_CLASS procedure. Dropping a job class means that all the metadata about the job class is removed from the database.
If there are jobs that belong to this job class, the DROP_JOB_CLASS call generates an error. The job class can still be dropped by setting the force option to TRUE, in which case the jobs belonging to the class are disabled and moved to the default class. If you drop a job class with a running job, the job continues running.
You can also 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;
/
Dropping a job class that does not exist will generate an error that will list the name of the job class that failed. Note that if it is not possible to drop jobclass2, then the DROP_JOB_CLASS call will fail. jobclass1 will be dropped but jobclass2 and jobclass3 will not be dropped.
Windows provide you with the functionality to activate different resource plans at different times. A resource plan is a component of the Resource Manager, which enables users to prioritize resources (most notably CPU) among resource consumer groups. The priorities are specified in a resource plan.
Each job class points to a resource consumer group and the same resource plan can thus be used to manager priorities among job classes. The next step is to provide different resource allocations at different times. For example, during the day, the finance group gets more resources, but at night, the admin group gets more resources.
The key attributes of windows are their:
schedules
These control when the window is in effect.
durations
These control how long the window is open.
resource plans
These control the resource priorities among the job classes
Only one window can be in effect at any given time, and a window is described as open if it is in effect. There is only one resource plan active for each window. Running jobs can see a change in the resources that are allocated to them when there is a change in resource plans. All window activity is written to the window log. Windows belong to the SYS schema.
This section introduces you to basic window tasks, and discusses the following topics:
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 "How to Manage Scheduler Privileges" for further information regarding privileges.
You create windows by using the CREATE_WINDOW procedure. When creating a window, you can specify the schedule for the window. Alternatively, you can also create a window that points to a saved schedule instead of inlining it during the window creation. The following statement creates a window called my_window1 that uses a resource plan of my_resourceplan1 and repeats every midnight for an hour:
BEGIN DBMS_SCHEDULER.CREATE_WINDOW ( window_name => 'my_window1', start_date => '01-JAN-03 12:00:00AM', repeat_interval => 'FREQ=DAILY', resource_plan => 'my_resourceplan1', duration => interval '60' minute, comments => 'My window'); END; /
Windows are created in the SYS schema. The Scheduler does not check if there is already a window defined for that schedule. Therefore, this may result in windows that overlap.
You can also create a window by pointing to a saved schedule instead of inlining its schedule. To create a window using a saved schedule, use the version of the CREATE_WINDOW procedure that has the schedule_name argument.
You can use any saved schedule to create a window because all schedules are created with access to public. For example, the following statement creates a window with a saved schedule:
BEGIN DBMS_SCHEDULER.CREATE_WINDOW ( window_name => 'my_window100', schedule_name => 'my_stats_schedule', resource_plan => 'my_resourceplan1', duration => interval '160' minute, comments => 'My window'); END; /
Using a saved schedule that has a PL/SQL expression as its repeat interval is not supported for windows. The CREATE_WINDOW call will fail in this case.
See PL/SQL Packages and Types Reference for further details about CREATE_WINDOW and "Configuring the Scheduler".
You alter a window using the SET_ATTRIBUTE procedure. 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 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:
Based on a schedule
A window will open based on the schedule that is defined during its creation.
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.
If you try to open a window that does not exist, an error is generated. If you try to open a window, and there is already an open window, then you will get an error. However, 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.
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.
A window can fail to open if the resource plan has been manually switched using the ALTER SYSTEM statement with the force option.
When a window is opened, there will be an entry in the *_SCHEDULER_WINDOW_LOG views to indicate this.
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.
You drop a window using the DROP_WINDOW procedure. 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 also 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.
Note that if it is not possible to drop window2 in this example, then the DROP_WINDOW call will fail. window1 will be dropped but window2, window3, windowgroup1, and windowgroup2 will not be dropped.
If the window is open, the DROP_WINDOW call generates an error unless the force option is set to TRUE in the procedure call. If this is the case, the window will be closed then dropped. When the window is closed, normal close window rules apply.
If there are jobs that have the window as their schedule, you will not be able to drop the window unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the window is dropped, those jobs that have the window as their schedule will be disabled. However, jobs that have a window group of which the dropped window was a member as their schedule will not be disabled.
Running jobs that have the window as their schedule will be allowed to continue, unless the stop_on_window_close flag was set to TRUE when the job was created. If this is the case, the job will be stopped when the window is dropped.
You disable a window using the DISABLE procedure. 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.
Disabling a window that is open will cause an error unless the force option is set to TRUE in the procedure call. If force is set to TRUE, disabling a window that is open will succeed but will not close the window. It will prevent the window from opening in the future until it is reenabled.
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;
/
Note that if a window group name is specified, then the window group will be disabled, but the windows that are members of the window group, will not be disabled. A job, other than a running job, that has the window group as its schedule will not run because the window group is disabled. However, if the job had one of the window group members as its schedule, it would still run.
Disabling a window that is already disabled does not generate an error. Disabling a window that does not exist causes an error. Also, if it is not possible to disable window2, the DISABLE call will fail. window1 will be disabled but window2, window3, windowgroup1, and windowgroup2 will not be disabled.
When a window is disabled, an entry is made in the window log.
You enable a window using the ENABLE procedure. 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 or window group names to the ENABLE procedure call. For example, the following statement enables both windows and window groups:
BEGIN
DBMS_SCHEDULER.ENABLE ('sys.window1, sys.window2,
sys.window3, sys.windowgroup1, sys.windowgroup2');
END;
/
Note that if a window group name is specified, then the window group will be enabled, but the windows that are members of the window group, will not be enabled.
Note that if it is not possible to enable window2, then the ENABLE call will fail and the error returned will list the name of the window or window group that failed. window1 will be enabled but window2, window3, windowgroup1, windowgroup2 will not be enabled.
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.
If at the end of a window there are multiple windows defined, 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.
For the Scheduler to successfully change resource plans, you must ensure that Resource Manager is active. To verify that it is, use the V$RSRC_PLAN view. If Resource Manager is not running, you need to set the RESOURCE_MANAGER_PLAN initialization parameter in the init.ora file or issue an ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = my_plan statement. Either option will activate Resource Manager and will set the default resource plan. If the Scheduler cannot switch resource plans, the appropriate window will still open and jobs that have that window as their schedule will be picked up.
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.
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.
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 activity is logged in the *_SCHEDULER_WINDOW_LOG views. See "Window Logs" for examples of window logging.
A window group is a named collection of windows. Window groups reside in the SYS schema. This section introduces you to basic window group tasks, and discusses the following topics:
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 |
| Enabling a window group | ENABLE |
MANAGE SCHEDULER |
| Disabling a window group | DISABLE |
MANAGE SCHEDULER |
See "How to Manage Scheduler Privileges" for further information regarding privileges.
You create a window group by using the CREATE_WINDOW_GROUP procedure. Only a window can be a member of a window group. You can specify the windows that will be members of the group when you are creating 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 window that does not exist as its member, an error is generated and the window group is not created.
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.
As an example, the following statement creates a window group called my_window_group1:
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_window_group1');
END;
/
Then, you could add a window (my_window1) to my_window_group1 by issuing the following statement:
BEGIN DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ( window_name => 'my_window_group1', window_list => 'my_window1'); END; /
You drop a window group by using the DROP_WINDOW_GROUP procedure. 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 also 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;
/
Dropping a window group that does not exist will cause an error stating that the window group does not exist and the error mess will contain the name of the window group that failed. Note that if it is not possible to drop windowgroup2 in the preceding example, then the DROP_WINDOW_GROUP call will fail. windowgroup1 will be dropped but windowgroup2 and windowgroup3 will not be dropped.
If there are jobs that have the window group as their schedule, you will not be able to drop the window group unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When a window group is dropped, those jobs that have the window group as their schedule will be disabled.
Running jobs that have the window group as their schedule are allowed to continue, even if the stop_on_window_close flag was set to TRUE when the job was created.
If a member of the window group that is being dropped is open, the window group can still be dropped.
See PL/SQL Packages and Types Reference for detailed information about adding and dropping window groups.
You add a member to a window group by using the ADD_WINDOW_GROUP_MEMBER procedure. Only when a window opens will the Scheduler check whether there are any jobs whose schedule is that window or a window group of which this window is a member. Based on priority and resource availability, the Scheduler will then execute the jobs.
If a window is already open, and a new job is created that points to that window, it will not be started until the next time the window opens. If an already open window is added to a window group, the Scheduler will not pick up jobs that point to this window group until the next window in the window group opens.
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:
BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ('window_group1',
'window1, window2, window3');
END;
/
If any of the windows specified is either invalid or does not exist, the call fails.
Note that a window group cannot be a member of another window group.
You can drop a window from a window group by using the REMOVE_WINDOW_GROUP_MEMBER procedure. Jobs with the stop_on_window_close flag set will only be stopped when a window closes. Dropping an open window from a window group has no impact on this.
You can remove several members from a window group in one call by specifying a comma-delimited list of windows. For example, the following statement drops three windows:
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER('window_group1', 'window1, window2,
window3');
END;
/
If any of the windows specified is either invalid or does not exist, the call fails.
You enable a window group using the ENABLE procedure. By default, window groups are created ENABLED. You can enable several window groups in one call by providing a comma-delimited list of window group names to the ENABLE procedure call. For example, consider the following statement:
BEGIN
DBMS_SCHEDULER.ENABLE('sys.windowgroup1', 'sys.windowgroup2, sys.windowgroup3');
END;
/
In this example, the window groups will be enabled but the windows that are members of the window groups will not be enabled.
Note that if it is not possible to enable windowgroup2, then the ENABLE call will fail. windowgroup1 will be enabled but windowgroup2 and windowgroup3 will not be enabled. The error message in this case will list the name of the window group that failed.
Enabling a window group that is already enabled does not generate an error.
You disable a window group using the DISABLE procedure. 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.
If a member of the window group that you are trying to disable is open, then an error occurs unless the force option is set to TRUE in the procedure call. If the window group is disabled, the open window will be not closed or disabled. It will be allowed to continue to its end.
If there are jobs that have the window group as their schedule, you will not be able to disable the window group unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the window group is disabled, those jobs that have the window group as their schedule will not be disabled.
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.
Note that if it is not possible to disable windowgroup2, then the DISABLE call will fail. windowgroup1 will be disabled but windowgroup2 and windowgroup3 will not be disabled. The error message in this case will list the name of the window group that failed. Disabling a window group that is already disabled will not generate an error.
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 mapping a job class 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 is specified when a job class is created, the job class will map to the default consumer group. Because the consumer group is an attribute of a job class, it can be changed after the job class has been created using the SET_ATTRIBUTE procedure.
Because all jobs must belong to a job class and a job class is always associated with a resource consumer group, resource manager will always be able to properly allocate resources among jobs.
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. Therefore, the job coordinator only starts jobs if there are enough resources available to run them.
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 till 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 it was scheduled to. 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 ALTER SYSTEM statement. In special scenarios, a database administrator might want to run a specific resource plan without the Scheduler switching to its Scheduler resource plans associated with windows. To do this, use the ALTER SYSTEM SET RESOURCE_MANAGER_PLAN statement with the force option.
|
Note: You must ensure that Resource Manager is active. Otherwise, the Scheduler will not be able to switch resource plans.To verify that it is, use theV$RSRC_PLAN view. If Resource Manager is not running, you need to set the RESOURCE_MANAGER_PLAN initialization parameter in the init.ora file or issue an ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = my_plan statement. |
In a RAC 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 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.
Note that a resource plan that specifies 100% of the resources to the consumer group Other is not the same as a resource plan that equally splits the resources among all consumer groups. In the first case, there is no active resource management. In the second case, Resource Manager will actively try to allocate resources equally among all the consumer groups. As an example, (CG DW 50% and CG OLTP 50%) do not equal Other 100%.
|
 Copyright © 2001, 2003 Oracle Corporation All Rights Reserved. |
|
