Oracle Scheduling Job Classes

Create A Job Class

A Job Class defines a category of jobs that share common resource usage requirements.  A Job Class is associated with two attributes: the resource consumer group, which defines a set of user sessions that have common resource processing requirements; and a database service name, which defines the instance to which the job class belongs.  Each job belongs to a single job class at any given time.  By associating a Job with a Job Class, you can manage the amount of resources a Job can use during its execution.

The syntax to create a Job Class is:

DBMS_SCHEDULER.CREATE_JOB_CLASS
(
 job_class_name           in varchar2,
 resource_consumer_group  in varchar2 default null,
 service                  in varchar2 default null,
 log_purge_policy         in varchar2 default null,
 comments                 in varchar2 default null
);

By default, the Scheduler log table entries are not purged. The log_purge_policy defines the policy for purging the log table entries.

Job Classes

Job classes allow the grouping of jobs with similar characteristics and resource requirements to ease administration.  If the job_class parameter of the create_job procedure is undefined, the job is assigned to a job class called DEFAULT_JOB_CLASS.

Job classes are created using the create_job_class procedure listed below.

PROCEDURE create_job_class(
  job_class_name          IN VARCHAR2,
  resource_consumer_group IN VARCHAR2     DEFAULT NULL,
  service                 IN VARCHAR2     DEFAULT NULL,
  logging_level           IN PLS_INTEGER  DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
  log_history             IN PLS_INTEGER  DEFAULT NULL,
  comments                IN VARCHAR2     DEFAULT NULL)

The parameters associated with this procedure and their usage are as follows.

* job_class_name - A name that uniquely identifies the job class.

* resource_consumer_group - The resource consumer group associated with the job class.

* service - The service database object the job belongs to, not the tnsnames.ora service.

* logging_level - The amount of logging that should be done for this job, specified by the constants logging_off, logging_runs, and logging_full.

* log_history - The number of days the logging information is kept before purging.

* comments - Free text allowing the user to record additional information.

Suffice it to say that it must be decided with which resource consumer group the job class should be associated.  Information about resource consumer groups can be displayed using the dba_rsrc_consumer_groups view.

select
   consumer_group
from
   dba_rsrc_consumer_groups
;

CONSUMER_GROUP
------------------------------
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
AUTO_TASK_CONSUMER_GROUP

5 rows selected.

With this information a new job class can be defined as follows:

BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name          => 'test_job_class',
    resource_consumer_group => 'default_consumer_group');
END;
/

Figure 2.11 shows the Create Job Class screen in the OEM 10g DB Control.

Figure 2.11 ? OEM 10g DB Control: Create Job Class

Information about job classes can be displayed using the dba_scheduler_job_classes view.  The following script uses this view:

* job_classes.sql

select
   job_class_name,
   resource_consumer_group
from
   dba_scheduler_job_classes
;

The output from the job_classes.sql script is displayed below.

SQL> @job_classes

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP
TEST_JOB_CLASS                 DEFAULT_CONSUMER_GROUP

3 rows selected.

Figure 2.12 shows the Scheduler Job Classes screen in the OEM 10g DB Control.

Figure 2.12 ? OEM 10g DB Control: Scheduler Job Classes

Jobs can be assigned to a job class during creation.  It is also possible to assign a job to an alternative job class after creation using one of the set_attribute procedure overloads.

BEGIN
  -- Job defined and assigned to a job class.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_class_job_def',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    job_class     => 'test_job_class',
    enabled       => TRUE,
    comments      => 'Job defined and assigned to a job class ');
END;
/
BEGIN
  -- Assign an existing job to a job class.
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_prog_sched_job_definition',
    attribute => 'job_class',
    value     => 'test_job_class');
END;
/

The output from the jobs_10g.sql script shows that the job classes associated with these jobs has been set correctly.

SQL> @jobs_10g job_user

OWNER      JOB_NAME                       JOB_CLASS        ENABLE
---------- ------------------------------ ---------------- ------
NEXT_RUN_DATE
-----------------------------------------------------------------
REPEAT_INTERVAL
-----------------------------------------------------------------
JOB_USER   TEST_FULL_JOB_DEFINITION       DEFAULT_JOB_CLASS  TRUE
22-JUN-04 15.00.08.900000 +01:00
freq=hourly; byminute=0

JOB_USER   TEST_PROG_SCHED_JOB_DEFINITION TEST_JOB_CLASS     TRUE
22-JUN-04 15.00.16.200000 +01:00

JOB_USER   TEST_PROG_JOB_DEFINITION       DEFAULT_JOB_CLASS  TRUE
22-JUN-04 15.00.09.600000 +01:00
freq=hourly; byminute=0

JOB_USER   TEST_SCHED_JOB_DEFINITION      DEFAULT_JOB_CLASS  TRUE
22-JUN-04 15.00.16.200000 +01:00

JOB_USER   ARGUMENT_JOB_DEFINITION        DEFAULT_JOB_CLASS  TRUE
22-JUN-04 15.00.16.200000 +01:00

JOB_USER   TEST_PROG_SCHED_CLASS_JOB_DEF  TEST_JOB_CLASS     TRUE
22-JUN-04 15.00.16.200000 +01:00

6 rows selected.

Job classes can be removed using the drop_job_class procedure listed below:

PROCEDURE drop_job_class (
  job_class_name          IN VARCHAR2,
  force                   IN BOOLEAN DEFAULT FALSE)

The parameters associated with this procedure and their usage are as follows:

* job_class_name - A name that specifies a single or comma separated list of job class names.

* force - When set to TRUE, all jobs that are assigned to the job class are disabled and have their job class set to the default.  When set to FALSE, attempting to drop a job class that has dependant jobs will cause an error.

The following code example shows how a job class can be removed.

BEGIN
  DBMS_SCHEDULER.drop_job_class (
    job_class_name => 'test_job_class',
    force          => TRUE);
END;
/

The output of the job_classes.sql script shows that the job class has been removed successfully.

SQL> @job_classes

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP

2 rows selected.

Now that to the creation of job classes has been presented, the next section will cover windows, another type of scheduler administration object.

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.