Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Scheduling Job Classes

Oracle Tips by Burleson Consulting

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational