Configuring the Scheduler - Oracle 10g

The following tasks are necessary when configuring the Scheduler:

Task 1: Setting Scheduler Privileges
Task 2: Configuring the Scheduler Environment

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.

Task 2: Configuring the Scheduler Environment

This section discusses the following tasks:

Task 2A: Creating Job Classes
Task 2B: Creating Windows
Task 2C: Creating Resource Plans
Task 2D: Creating Window Groups
Task 2E: Setting Scheduler Attributes

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:

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;Creating Job ClassesTask 2B: Creating Windows

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

This statement creates a window called my _window1 with attributes such as a resource plan of my _resourceplan1. To verify the window contents, 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;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. If you do not create a resource plan, the Scheduler uses a default resource plan called INTERNAL _PLAN.

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

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;view DBA _RSRC _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:

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;Creating Window Groups SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;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, and max _job _slave_ processes. It is crucial that you set the default _timezone attribute because it impacts the behavior of repeating jobs and windows. The other two have defaults, but you may want to change the default settings. The values of these attributes can be set by using the SET _SCHEDULER _ATTRIBUTE procedure. Setting these attributes requires the MANAGE SCHEDULER privilege. Attributes that can be set are:

  • default_timezone

Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. This is normally retrieved from start _date, but if no start_date is provided (which is not uncommon), the time zone is retrieved from this Scheduler attribute. To make sure that daylight savings adjustments are followed, it is strongly recommended to set this attribute to a region name instead of an absolute time zone offset. For example, if your database resides in Miami, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');

If you do not set this attribute, the default time zone for repeating jobs and windows will be the absolute offset retrieved from SYSTIMESTAMP (the time zone of the OS environment of the database), which means that repeating jobs and windows that do not have their start _date set will not follow daylight savings adjustments.

  • log_history

This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler automatically purges all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days.

You can change the default by using the SET_SCHEDULER_ATTRIBUTE procedure. For example, to change it to 90 days, issue the following statement

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

The range of valid values is 1 through 999.

  • max_job_slave_processes

This enables you to set a maximum number of slave processes for a particular system configuration and load. Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute. The default value is NULL, and the valid range is 1-999.

Although the number set by max _job _slave _processes is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics