Skip H eaders

Oracle® Database Administrator's Guide
10g Release 1 (10.1)< br /> Part Number B10739-01
Go to Documentat ion Home
Home		 < img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0" />
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master In dex		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
View PDF

27 Using the Scheduler

Oracle Database provides database scheduling capabilities thr ough the Scheduler. This chapter introduces you to its use, and discusses the following topics:

Scheduler Objects and The ir Naming

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 Sch eduler objects are used in the DBMS_SCHEDULER package, SQL naming rules continue to be followed. By default, Scheduler o bject names are uppercase unless they are surrounded by double quotes. For example, when creating a job, its name must be provided. < code>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 li sts of Scheduler object names are used within the DBMS_SCHEDULER package.

See Oracle Database SQL Reference for details regarding naming objects.

Administering Jobs

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

Job Tasks and Their Procedures

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

Table 27-1 Job Tasks and Their Procedures< /em>

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 t he 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 th e 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 informat ion regarding privileges.

Creating Jobs

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, whic h 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 Austra
lia/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 schem a 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 an d they need to be enabled in order to be executed.

Job Attributes

Some job attributes are set at job creation time, while o ther job attributes are not. Instead, you can specify these attributes after the job has been created by using the SET_ATTRIBUT E procedure. See PL/SQL Packages and Type s Reference for information about the SET_ATTRIBUTE procedure.

Setting Job Arguments

After creating a job, you may need to set job arguments. To set job arguments, us e the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures. Both procedures have the same purpose, bu t SET_JOB_ANYDATA_VALUE is used for types that cannot be implicitly converted to and from VARCHAR2. A typic al 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 doe s 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 a n 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 ty pe plsql_block.

To remove a value that has been set, us the RESET_JOB_ARGUMENT procedure. This proce dure 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 a nd their syntax.

Ways of Creating Jobs

You create a job using the CREATE_JOB proc edure. Because this procedure is overloaded, there are several different ways of using it. In addition to inlining a job during the j ob creation, you can also create a job that points to a saved program and schedule. This is discussed in the following sections:

Creating Jobs Using a Saved Program

You can also create a job b y 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 owne r 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_p
rogram', 
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/
Creating Jobs Using a Saved Schedule

You can also create a job by poi nting to a saved schedule instead of inlining its schedule. To create a job using a saved schedule, you specify the value for s chedule_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 s chedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a saved sched ule is the following statement, which creates a job called my_new_job2:

BEGIN
DBMS_SCHEDUL
ER.CREATE_JOB (
   job_name                 =>  'my_new_job2', 
   job_type                 =>  'PLSQL_BLOCK',
   job_action
            =>  'BEGIN foo(); END;',
   schedule_name            =>  'my_saved_schedule');
END;
/
Creating Jobs Using a Saved Program and Schedule

A job can also be created by po inting to both a saved program and schedule. An example of using the CREATE_JOB procedure with a saved program and sched ule 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;
/

Copying Jobs

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

Privileges Required for Copying a Job

You can copy a job if you are the owner of the job, or have ALTER privileges on the job, or have the CREATE ANY JOB privilege. Only SYS can copy a job from or into the SYS schema.

Altering Jobs

You alter a job using the SET_ATTRIBUTE procedure. All jobs can be altered, and, with the exception of the job name, al l job attributes can be changed. If an enabled job is altered, the Scheduler will disable it, make the change and then reenable it. I f any errors are encountered during the enable process, the job is not enabled and an error is generated. If there is a running insta nce of the job when the SET_ATTRIBUTE call is made, it is not affected by the call. The change is only seen in future ru ns 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 sched uled 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 r unning, the new program attributes will take effect the next time the job runs. Program attributes of a job include program_nam e, job_action, job_type, and number_of_arguments. This is also the case for job argumen t 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 genera l, 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".

Running Jobs

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

Running Jobs Asynchronously

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

Running Jobs Synchronously

Once a job has been created, you can run the job sy nchronously 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 coor dinator and being executed by a job slave. To run the job using the RUN_JOB procedure, it must be enabled.

You ca n use the RUN_JOB procedure to test a job, thereby ensuring that it runs without errors. It can also be used to run a jo b 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_c ount 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.

Job Run Environment

Jobs ar e 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 privile ge and creates a job in the scott schema, then the job will run with the privileges of scott.

The NL S 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 envi ronment in which a job runs, a job must be created in a session with different NLS settings.

Running External Jobs

An external job is a job that runs outside the database. All external job s run as a low-privileged guest user, as has been determined by the database administrator while configuring external job support. Be cause the executable will be run as a low-privileged guest account, you should verify that it has access to necessary files and resou rces. Most, but not all, platforms support external jobs. For platforms that do not support external jobs, creating or setting the at tribute 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 progr am_action if using named programs) is the full OS-dependent path of the desired external executable plus optionally any comman d line arguments. For example, /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for ty pe EXECUTABLE must be a string type such as CHAR, VARCHAR2, or VARCHAR.

So me additional post-installation steps might be required to ensure that external jobs run as a low-privileged guest user. See your ope rating system-specific documentation for any post-installation configuration steps.

Setting Environ ment Variables for External Jobs

To ensure that environment variables can be used with external jobs, you can use a wr apper such as Perl or sh before invoking the external job. As an example, if you have an external job (hello.exe) with < code>USER_NAME and LOCATION environment variables, you could create a perl wrapper (hello.pl) such a s the following:

$ENV { "USER_NAME" } = $ARGV[1];
$ENV { "LOCATION" } = $ARGV[2];
system ($ARGV[0]);

<
/pre>


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). You
r program arguments would be the following:


"C:\home\hello.exe"     --- the path where hello.exe is loc
ated
"Myname"                --- the value for USER_NAME
"Mytown"                --- the value for LOCATION

Stopping Jobs

Yo u stop a running job using the STOP_JOB procedure. Job classes reside in the SYS schema, therefore, wheneve r job classes are used in comma-delimited lists, they must be preceded by SYS. For example, the following statement stop s 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 repea ting 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 j ob to STOPPED.

If the interrupt is not successful, the STOP_JOB call will fail. The job will be stop ped 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 j obs 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 th at 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 sto pped, only the current transaction will be rolled back. Note that if there are commits in the executable that the job is running, the n only the current transaction will be rolled back. This can cause data inconsistency.

Dropping Jobs

You drop a job usi ng the DROP_JOB procedure. Dropping a job results in the job being removed from the job table, its metadata being remove d, 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 settin g 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 combine s both jobs and job classes:

BEGIN
DBMS_SCHEDULER.DROP_JOB ('job1, job2, job3, 
   sys.jobclass1, sys.j
obclass2, 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 dro p 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 s pecified 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 attemp ted 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.

Disabling Jobs

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 belong s 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 jo b 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 c urrently running, an error is returned. When force is set to TRUE, the job is disabled, but the currently r unning 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 cla sses:

BEGIN
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
END;
/
< p>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.

Enabling Jobs

You enable jobs by using the ENABLE procedure. The effec t of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by d efault, 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 nam es 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;
/

Not e 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 j ob2, then the ENABLE call will fail. job1 will be enabled but job2, job3, jobclass1, and jobclass2 will not be enabled.

See "How to Manage Scheduler Privileges" for further information regarding privileges.

Creating Programs

You create programs by using the CREATE_PROGRAM procedure. By defau lt, 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, there fore, once a program has been created, you have to grant the EXECUTE privilege on it. An example of creating a program i s the following, which creates a program called my_program1:

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

< font face="arial, helvetica, sans-serif">See Also:

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

Defining Program Arguments

After creating a program, you will want to define program arguments whe n 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 whe re 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 add s 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_posi
tion       => 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_po
sition       => 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 possi ble 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:

< a id="i1022924" name="i1022924">

Altering Programs

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 wit h the new program definition the next time the job executes.

When a program is altered and it was in the enabled state, the Sc heduler first disables it, applies the change, and then reenables it. If any errors are encountered during the enable process, the pr ogram is not reenabled and an error is generated.

The SET_ATTRIBUTE_NULL is only required for setting the value o f any program attribute to NULL.

Dropping Programs

You drop a program using the DROP_PROGRAM procedure. You can also drop several programs in one call by providin g 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 doe s 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 w ill not be able to drop the program unless you set force to TRUE in the procedure call. By default, f orce 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.

An y arguments that pertain to the program are also dropped when the program is dropped.

Disabling Programs

You disable a program using the DISABLE procedure. When a program is d isabled, 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 o f 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 ge nerates an error stating that the program does not exist. Note that if it is not possible to disable program2 in this ex ample, then the DISABLE call will fail. program1 will be disabled but program2 and progr am3 will not be disabled.

If there are jobs that point to the program that you are trying to disable, you will not be a ble 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, t he 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 whe n 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.

Enabling Programs

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 the m 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 ex ist will cause an error stating that the program does not exist. Note that if it is not possible to enable program2 in t his example, then the ENABLE call will fail. program1 will be enabled but program2 and, program3 will not be enabled.

Adm inistering Schedules

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:

Schedule Tasks and Their Procedures

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

Table 27-3 Schedule Tasks and Their Procedures

< td align="left" headers="r2c1-t7 r1c3-t7">CREATEJOB or CREATEANYJOB

See "How to Manage Scheduler Privileges" for fu rther information regarding privileges.

Creating Schedules

You create schedules by using the CREATE_SCHEDULE procedure. Schedules are created in the schema of the user creatin g the schedule, and are enabled when first created. You can create a schedule in another user's schema. Once a schedule has been crea ted, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explic itly 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;
/

Altering Schedules

You alter a sc hedule by using the SET_ATTRIBUTE procedure. Altering a schedule changes the definition of the schedule. With the except ion 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 w ill only be in effect the next time the jobs runs or the window opens.

Dropping Schedules

You drop a schedule using the DROP_SCHEDULE procedure. This procedure call will delet e the schedule object from the database. You can also drop several schedules in one call by providing a comma-delimited list of sched ule 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 windo ws that use this schedule, the DROP_SCHEDULE call will fail unless you set force to TRUE. If y ou set force to TRUE, then the jobs and windows that use this schedule will be disabled before the schedule is dropped.

Setting the Repeat Interval

You control ho w often a job repeats by setting the repeat_interval attribute. The expression specified is evaluated to determine the n ext time the job should run. If no value for repeat_interval is specified, the job will run only once at the specified s tart date.

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

There are two ways to specify the repeat interval:

Using the Scheduler Calendaring Syntax

The primary method of setting how often a job will repeat is by setting the repeat_interval attribute with Oracle Da tabase calendaring expression. See PL/SQL Packag es and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure.


Examples of Using Cale ndaring Expressions

The following examples illustrate simple tasks.

Execute every Friday.

FREQ=WEEKLY; BYDAY=FRI;

Execute every other Friday.

FREQ=WEEKLY; INTERVA
L=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; BYMONTHD
AY=15;

Execute on the 29th day of every month.

FREQ=MONTHLY; BYMONTHDAY=29;

Exe cute on the second Wednesday of each month.

FREQ=MONTHLY; BYDAY=2WED;

Execute on the last F riday 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=MONTHL
Y; INTERVAL=2; BYMONTHDAY-1;

Execute hourly for the first three days of every month.

FREQ=H
OURLY; 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:0
0
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
...

<
/pre>


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


WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 1
5-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_nam
e             => 'scott.my_job1',
   start_date           => '15-JUL-03 01.00.00 AM Europe/Warsaw',
   repeat_interval      =&g
t; '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 Ju
ly 15th and then run until September 15. The job is run every 30 minutes.

Using a PL/SQL Expression

When you need more complicated capabilities than the calendaring syntax provides, you ca n use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or named schedules. The PL/SQL expression must eval uate 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.C
REATE_JOB (
   job_name             => 'scott.my_job2', 
   start_date           => '15-JUL-03 01.00.00 AM Europe/Warsaw',
   r
epeat_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 w ill run for the first time on July 15th and then every 30 minutes until September 15. The job is run every 30 minutes because r epeat_interval is set to SYSTIMESTAMP + INTERVAL '30' MINUTE, which returns a date 30 minutes into the future.

< h4>Differences Between PL/SQL Expression and Calendaring Syntax Behavior

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 sc hedule 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 st art 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 n ext 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 j ob started at 2:00 PM and its schedule was to repeat every 2 hours, then, if the repeat interval was specified with the calendaring s yntax, 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 t he next job actually started at 4:11, then the subsequent run would be at 6:11.

To illustrate these two points, cons ider 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-JU
L-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 + interv al '2' hour", however, might have a run time of the following:

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

Repeat Intervals and Daylight Savings

For repeating jobs, the next time a job is sc heduled 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 express ion. In both cases, it is important to use region names. For example, "Europe/Istanbul", instead of absolute time zone o ffsets such as "+2:00". Only when a time zone is specified as a region name will the Scheduler follow daylight savings a djustments that apply to that region.

Administering Job Classes

Jobs can be difficult to manage on an individual basis, so the S cheduler uses job classes, which group jobs with common characteristics and behavior together. Prioritization among job classes is po ssible using resource plans.

There is a default job class that is created with the database. If you create a job without speci fying 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 j ob class tasks, and discusses the following topics:

Job Class Tasks and Their Procedures

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

Table 27-4 Job Class Tasks and Their Procedures< /em>

Task Procedure Privilege Needed
Create a schedule CREATE_SCHEDULE
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
< /thead>
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.

Creating Job Classes

You create a job class using the CREATE_JOB_CLASS procedure. F or example, the following statement creates a job class for all finance jobs:

BEGIN
DBMS_SCHEDULER.CREA
TE_JOB_CLASS (
   job_class_name             =>  'finance_jobs', 
   resource_consumer_group    =>  'finance_group');
END;
/

<
/pre>


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 use
rs 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 informa
tion about the SET_ATTRIBUTE procedure and "Configuring the Scheduler" for example
s of creating job classes.

Altering 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 ca n 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 avai lable in the *_SCHEDULER_JOB_CLASSES views.

When a job class is altered, running jobs that belong to the class ar e 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 < code>SET_ATTRIBUTE procedure and "Configuring the Scheduler".

Dropping Job Classes

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 se tting 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 i n 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 fai l. jobclass1 will be dropped but jobclass2 and jobclass3 will not be dropped.

Administering Windows

Windows provide you with the functionality to activate different resource plans at different times. A resource plan is a component of the Resource Manag er, 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 prior ities 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 th eir:

  • schedules

    These control when the window is in effect.

  • durations

    These control how long the window is open.

  • resource plans

    These control the resource prio rities among the job classes

Only one window can be in effect at any given time, and a window is described as open i f 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 al located 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:

Window Tasks and Their Procedures

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

Table 27-5 Window Tasks and Their Procedures

< /tr>
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.

Creatin g Windows

You create windows by using the CREATE_WINDOW procedure. When creating a window, you can specif y the schedule for the window. Alternatively, you can also create a window that points to a saved schedule instead of inlining it dur ing the window creation. The following statement creates a window called my_window1 that uses a resource plan of m y_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 d efined for that schedule. Therefore, this may result in windows that overlap.

Creating Windows Usin g a Saved Schedule

You can also create a window by pointing to a saved schedule instead of inlining its schedule. To c reate 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 publi c. For example, the following statement creates a window with a saved schedule:

BEGIN
DBMS_SCHED
ULER.CREATE_WINDOW (
   window_name         =>  'my_window100',
   schedule_name       =>  'my_stats_schedule',
   resource_pla
n       =>  'my_resourceplan1',
   duration            =>  interval '160' minute,
   comments            =>  'My window');
E
ND;
/

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

Altering Windows

< p>You alter a window using the SET_ATTRIBUTE procedure. With the exception of WINDOW_NAME, all the attribut es of a window can be changed when it is altered. The attributes of a window are available in the *_SCHEDULER_WINDOWS vi ews.

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

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 s uccessful.

See PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure and "Con figuring the Scheduler".

Opening Windows

When a win dow opens, the Scheduler switches to the resource plan that has been associated with it during its creation. If there are jobs runnin g 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 du ring 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. Onl y 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 dura tion is not specified, then the window will be opened for the regular duration as stored with the window.

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

    When a window that was manually opened closes, the rules about ov erlapping 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 < code>TRUE in the OPEN_WINDOW call.

    When the force option is set to TRUE, the Sch eduler 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 th at 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 hou rs. 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. I f you specified a duration of 30 minutes, the window will close in 30 minutes.

A window can fail to open if the reso urce 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.

Closing Windows

There are two ways a window can close:

  • Based on a schedule

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

  • Manually, using the CLOSE_WINDOW procedure

    The CLOSE_WINDOW procedure will close an open win dow prematurely.

A closed window means that it is no longer in effect. When a window is closed, the Scheduler will s witch 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 h as 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 t he same window group then becomes active. This is the case even if the job was created with the attribute stop_on_window_close< /code> set to TRUE.

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

Dropping Windows

You drop a window using th e DROP_WINDOW procedure. When a window is dropped, all metadata about the window is removed from the *_SCHEDULER_W INDOWS views. All references to the window are removed from window groups.

You can also drop several windows in one cal l by providing a comma-delimited list of window names or window group names to the DROP_WINDOW procedure. For example, t he 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 window s 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_WIND OW 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 th eir schedule, you will not be able to drop the window unless you set force to TRUE in the procedure call. B y default, force is set to FALSE. When the window is dropped, those jobs that have the window as their sche dule 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_clo se 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.

Disabling Windows

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 m ust 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 wi ndow can also become disabled for other reasons. For example, a window will become disabled when it is at the end of its schedule. Al so, if a window points to a schedule that no longer exists, it becomes disabled.

If there are jobs that have the window as the ir 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 s chedule 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 wind ow 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 it s 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. w indow1 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.

Enabling Windows

You enable a window using the ENABLE procedure. An enabled wind ow is one that can be opened. Windows are, by default, created enabled. When a window is enabled using the ENABLE< /code> 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 bo th 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.

Overlapping Windows

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

  • If windows of the same p riority overlap, the window that is active will stay open. However, if the overlap is with a window of higher priority, the lower pri ority window will close and the window with the higher priority will open.

  • If at the end of a window the re 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.

Changing Resource Plans< /font>

For the Scheduler to successfully change resource plans, you must ensure that Resource Manager is active. To verify th at it is, use the V$RSRC_PLAN view. If Resource Manager is not running, you need to set the RESOURCE_MANAGER_PLAN< /code> initialization parameter in the init.ora file or issue an ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = < em>my_plan statement. Either option will activate Resource Manager and will set the default resource plan. If the S cheduler cannot switch resource plans, the appropriate window will still open and jobs that have that window as their schedule will b e picked up.

Examples of Overlapping Windows

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

< font face="arial, helvetica, sans-serif">Figure 27-1 Windows and Resource Plans (Example 1)

Description of admin064.gif follows
Descri ption of the illustration admin064.gif

In Figure 27-1, the following occurs:

  • From 12AM to 4AM

    No windows are open, so a default resource plan is in effect.< /p>

  • 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 beca use 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 Wind ow1 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.< /p>

  • From 2PM to 3PM

    Window2 will open so Resource Plan 2 is in effect.

  • F rom 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 sched ule.

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

Description of admin063.gif follows
Description of the illustration admin063.gif

In Figure 27-2, the followin g occurs:

  • From 12AM to 4AM

    A default resource plan is in effect.

  • Fro m 4AM to 6AM

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

  • From 6AM to 9AM

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

  • From 9AM t o 11AM

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

Window Logging

Window activity is logged in the *_SCHEDULER_WINDOW_LOG views. See "Window Logs" for examples of window logging.

Administering Window Groups

A window group is a named collection of windo ws. Window groups reside in the SYS schema. This section introduces you to basic window group tasks, and discusses the f ollowing topics:

Window Group Tasks and The ir Procedures

Table 27-6 illustrates common window group tasks and the procedures you use to h andle 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 M ANAGE SCHEDULER
Add a member to a window group ADD_WINDOW_GROUP_MEMBER MANAGE SCHEDULER
Drop a member to a window group REMOVE_WINDOW_G ROUP_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.

Creating Window Groups

You create a window group by using the CREATE_WINDOW_GROUP procedure. Only a wi ndow 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 wi ndow 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 th e 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< /code>:

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

Then, you cou ld 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;
/

Dropping Window Groups

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 member s 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 u se the DROP_WINDOW procedure and provide the name of the window group to the call.

You can also drop several wind ow 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 stat ing 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 the re 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.

Adding a Member to a Window Group

You add a member to a window group by using the ADD_WINDOW_GROUP_MEMBER proced ure. 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 win dow 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-delim ited 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.

Dropping a Member from a Window Group

You can drop a window from a wi ndow group by using the REMOVE_WINDOW_GROUP_MEMBER procedure. Jobs with the stop_on_window_close flag set w ill only be stopped when a window closes. Dropping an open window from a window group has no impact on this.

You can remove se veral members from a window group in one call by specifying a comma-delimited list of windows. For example, the following statement d rops 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.

Enabling a Window Group

You enable a window group using the ENA BLE 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 followin g 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 mes sage 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.

Disabling a Window Group

You disabl e 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 occ urs 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 grou p as their schedule, you will not be able to disable the window group unless you set force to TRUE in the p rocedure call. By default, force is set to FALSE. When the window group is disabled, those jobs that have t he 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.

Al locating Resources Among Jobs

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

< h3>Allocating Resources Among Jobs Using Resource Manager

Re source Manager is the database feature that controls how resources are allocated in the database. It not only controls asynchronous s essions like jobs but also synchronous sessions like user sessions. It groups all "units of work" in the database into resource consu mer 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 m anager.

For jobs, resource allocation is specified by mapping a job class to a consumer group. The consumer group that a job c lass 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 co mplete rather than running a lot of jobs concurrently but without enough resources for any of them to complete. Therefore, the job co ordinator 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 coordin ator determines how many jobs to start. It will only start jobs from those job classes that will have enough resources to run. The co ordinator will keep starting jobs in a particular job class that maps to a consumer group till Resource Manager determines that the m aximum 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 Reso urce Manager can only manage database processes. The active management of CPU cycles does not apply to jobs of type executable< /code>.

In a database, only one resource plan can be in effect at one time. It is possible to manually switch the resource pla n that is active on a system using the ALTER SYSTEM statement. In special scenarios, a database administrat or 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 mu st ensure that Resource Manager is active. Otherwise, the Scheduler will not be able to switch resource plans.To verify that it is, u se the V$RSRC_PLAN view. If Resource Manager is not running, you need to set the RESOURCE_MANAGER_PLAN init ialization parameter in the init.ora file or issue an ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = my _plan statement.

In a RAC environment, the sa me resource plan will be in effect on every database instance.

Example of Resource Allocation for Jobs

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.

Figure 27-3 Sample Resource Plan

Description of admin066.gif follows
Description of the il lustration admin066.gif

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 resou rces and can get a maximum of 10% of the resources.

Note that a resource plan that specifies 100% of the resources to the cons umer 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 equa lly among all the consumer groups. As an example, (CG DW 50% and CG OLTP 50%) do not equal Other 100%.

Go to previous pa ge
Previous		 Go to next page
Next
 Oracle
Copyright © 2001, 2003 Oracle Corporation
All Rights Reser ved.
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
C ontents		 Go to Ind ex
Index		 Go to Master Index
Master Index		 Go to Feedb ack page
Feedback