Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

B28310-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Configuring Oracle Scheduler

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

Task 1: Setting Scheduler Privileges

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

GRANT SCHEDULER_ADMIN TO username;

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

GRANT CREATE JOB TO scott;

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

GRANT MANAGE SCHEDULER TO adam;

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

Setting Chain Privileges

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

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

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

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

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

See Also:

Task 2: Configuring the Scheduler Environment

This section discusses the following tasks:

Task 2A: Creating Job Classes

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

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

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

SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;

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

5 rows selected.

Note that job classes are created in the SYS schema.

See Also:

Oracle Database PL/SQL Packages and Types Reference for CREATE_JOB_CLASS syntax, "Creating Job Classes" for further information on job classes, and "Examples of Creating Job Classes" for more examples of creating job classes

Task 2B: Creating Windows

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

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name     =>  'my_window1',
   resource_plan   =>  'my_resourceplan1',
   start_date      =>  '15-APR-03 01.00.00 AM Europe/Lisbon',
   repeat_interval =>  'FREQ=DAILY',
   end_date        =>  '15-SEP-04 01.00.00 AM Europe/Lisbon',
   duration        =>  interval '50' minute,
   window_priority =>  'HIGH',
   comments        =>  'This is my first window.');
END;
/

To verify that the window was created properly, query the view DBA_SCHEDULER_WINDOWS. As an example, issue the following statement:

SELECT WINDOW_NAME, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL
FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME    RESOURCE_PLAN     DURATION         REPEAT_INTERVAL
-----------    -------------     -------------    ---------------
MY_WINDOW1     MY_RESOURCEPLAN1  +000 00:50:00    FREQ=DAILY

See Also:

Oracle Database PL/SQL Packages and Types Reference for CREATE_WINDOW syntax, "Creating Windows" for further information on windows, and "Examples of Creating Windows" for more examples of creating job classes

Task 2C: Creating Resource Plans

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

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

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

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

SELECT PLAN, STATUS FROM DBA_RSRC_PLANS;

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

See Also:

"Allocating Resources Among Jobs" for further information on resource plans

Task 2D: Creating Window Groups

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

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

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

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

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

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

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

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

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

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

See Also:

Oracle Database PL/SQL Packages and Types Reference for CREATE_WINDOW_GROUP syntax, "Using Window Groups" for further information on window groups, and "Example of Creating Window Groups" for more detailed examples of creating window groups

Task 2E: Setting Scheduler Attributes

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

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