This blog is part of a series about aspects and features of Oracle 26 Autonomous Transaction Processing Database.
I have written about using Job Classes with the database scheduler. It is essentially the same on Autonomous database, but some configuration is delivered by Oracle. You may choose to use it directly as delivered. However, I suggest using it as the basis for a custom configuration.
The Autonomous Transaction Processing (ATP) database is delivered with 5 consumer groups and 5 corresponding job classes that map to them.
OWNER JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP SERVICE
----- -------------- ----------------------- ------------------------------------------------------
LOGGING_LEVEL LOG_HISTORY COMMENTS
------------- ----------- ----------------------------------------
SYS TPURGENT TPURGENT GE***********09_GOFASTER1_tpurgent.adb.oraclecloud.com
RUNS Urgent transaction processing jobs
SYS TP TP GE***********09_GOFASTER1_tp.adb.oraclecloud.com
RUNS Transaction processing jobs
SYS HIGH HIGH GE***********09_GOFASTER1_high.adb.oraclecloud.com
RUNS High priority jobs
SYS MEDIUM MEDIUM GE***********09_GOFASTER1_medium.adb.oraclecloud.com
RUNS Medium priority jobs
SYS LOW LOW GE***********09_GOFASTER1_low.adb.oraclecloud.com
RUNS Low priority jobs
It is easy and perfectly reasonable to allocate these delivered job classes to scheduler jobs. However, these job classes cannot be changed, even by the ADMIN user.BEGIN dbms_Scheduler.set_attribute('SYS.TPURGENT', 'comments', 'A Comment'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_ISCHED", line 3513 ORA-06512: at "SYS.DBMS_SCHEDULER", line 3460 ORA-06512: at line 1
Note that the service names are different and unique to every autonomous database. I have been careful to avoid hard-coding this anywhere within my scripts and code. Instead, I duplicate the delivered job classes and then alter as necessary. Thus, each job or group of jobs has its own job class. I prefer to manage the job and the job scheduler, as far as possible, within a packaged procedure. This has several advantages.
- The right version is always available because it has been installed into the database and can be migrated like any other version-controlled source code. This also covers when a database has been cloned, restored or flashed back. This saves looking for the right version of the right script.
- Jobs can be created and managed by a user who does not have access to manage the job scheduler, but who can execute procedures in the package.
- I have created a procedure to clone a job class and adjust attributes as required.
- The code is available on GitHub.
CREATE OR REPLACE PACKAGE BODY strava.strava_job AS ... e_job_already_exists EXCEPTION; PRAGMA EXCEPTION_INIT(e_job_already_exists,-27477); ... PROCEDURE create_job_class (p_job_class_name all_scheduler_job_classes.job_class_name%TYPE ,p_based_on_job_class all_scheduler_job_classes.job_class_name%TYPE ,p_resource_consumer_group all_scheduler_job_classes.resource_consumer_group%TYPE DEFAULT NULL ,p_service all_scheduler_job_classes.service%TYPE DEFAULT NULL ,p_logging_level all_scheduler_job_classes.logging_level%TYPE DEFAULT NULL ,p_log_history all_scheduler_job_classes.log_history%TYPE DEFAULT NULL ,p_comments all_scheduler_job_classes.comments%TYPE DEFAULT NULL) IS r_job_class all_scheduler_job_classes%ROWTYPE; ... BEGIN ... SELECT * INTO r_job_class FROM all_scheduler_job_classes WHERE owner = 'SYS' AND job_class_name = p_based_on_job_class; BEGIN DBMS_SCHEDULER.CREATE_JOB_CLASS(p_job_class_name); EXCEPTION WHEN e_job_already_exists THEN NULL; END; IF p_resource_consumer_group IS NOT NULL THEN r_job_class.resource_consumer_group := p_resource_consumer_group; END IF; IF p_service IS NOT NULL THEN r_job_class.service := p_service; END IF; IF p_logging_level IS NOT NULL THEN r_job_class.logging_level := p_logging_level; END IF; IF p_log_history IS NOT NULL THEN r_job_class.log_history := p_log_history; END IF; IF p_comments IS NOT NULL THEN r_job_class.comments := p_comments; END IF; dbms_Scheduler.set_attribute(p_job_class_name, 'resource_consumer_group', r_job_class.resource_consumer_group); dbms_Scheduler.set_attribute(p_job_class_name, 'service' , r_job_class.service); IF r_job_class.logging_level = 'OFF' THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_OFF); ELSIF r_job_class.logging_level = 'RUNS' THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_RUNS); ELSIF r_job_class.logging_level = 'FAILED RUNS' THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS); ELSIF r_job_class.logging_level = 'FULL' THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL); END IF; dbms_Scheduler.set_attribute(p_job_class_name, 'log_history' , r_job_class.log_history); dbms_Scheduler.set_attribute(p_job_class_name, 'comments' , r_job_class.comments); ... EXCEPTION WHEN no_data_found THEN ... RAISE; END create_job_class;
This new procedure is called from the procedures that create jobs. In the example below, the LOW job class is cloned into a new PURGE_API_LOG_CLASS that is used by the PURGE_API_LOG job. I have set the log history retention to 7 days, but all other settings remain the same.
PROCEDURE create_purge_api_log_job IS k_job_name CONSTANT VARCHAR2(128 CHAR) := 'STRAVA.PURGE_API_LOG'; k_job_class CONSTANT VARCHAR2(128 CHAR) := 'SYS.PURGE_API_LOG_CLASS'; BEGIN ... create_job_class(k_job_class,'LOW', p_log_history=>7); BEGIN dbms_scheduler.create_job( (job_name => k_job_name ,job_type => 'STORED_PROCEDURE' ,job_action => 'STRAVA.STRAVA_HTTP.PURGE_API_LOG' ,enabled => FALSE ); EXCEPTION WHEN e_job_already_exists THEN NULL; END; ... dbms_scheduler.set_attribute(name => k_job_name, attribute => 'JOB_CLASS', value => k_job_class); ... dbms_scheduler.enable(name => k_job_name); ... END create_purge_api_log_job; ... END strava_job; /
Now I have several job classes
OWNER JOB_CLASS_NAME RESOURCE_CON SERVICE ----- ------------------------------------- ------------ ------------------------------------------------------ LOGGING_LEVEL LOG_HISTORY COMMENTS ------------- ----------- ------------------------------ SYS CREATE_ACTIVITY_HSEARCH_UPD_ALL_CLASS LOW GE***********09_GOFASTER1_low.adb.oraclecloud.com RUNS 7 Low priority jobs SYS ACTIVITY_AREA_LIST_UPD_ALL_CLASS LOW GE***********09_GOFASTER1_low.adb.oraclecloud.com RUNS 7 Low priority jobs SYS PURGE_API_LOG_CLASS LOW GE***********09_GOFASTER1_low.adb.oraclecloud.com RUNS 7 Low priority jobs SYS PURGE_EVENT_QUEUE_CLASS LOW GE***********09_GOFASTER1_low.adb.oraclecloud.com RUNS 7 Low priority jobs SYS BATCH_LOAD_ACTIVITIES_CLASS MEDIUM GE***********09_GOFASTER1_medium.adb.oraclecloud.com RUNS 7 Medium priority jobs SYS UPDATE_STRAVA_ACTIVTY_CLASS MEDIUM GE***********09_GOFASTER1_medium.adb.oraclecloud.com RUNS 7 Medium priority jobs SYS PROCESS_WEBHOOK_QUEUE_CLASS MEDIUM GE***********09_GOFASTER1_medium.adb.oraclecloud.com RUNS 7 Medium priority jobs SYS RENEW_STRAVA_TOKENS_CLASS HIGH GE***********09_GOFASTER1_high.adb.oraclecloud.com RUNS 7 High priority jobsEach job has been allocated to a different job class. In future, I can control the behaviour of each job by adjusting the job class.
OWNER JOB_NAME JOB_CLASS
------ ------------------------------------ -------------------------------------
STRAVA ACTIVITY_AREA_LIST_UPD_ALL_JOB ACTIVITY_AREA_LIST_UPD_ALL_CLASS
STRAVA BATCH_LOAD_ACTIVITIES_JOB BATCH_LOAD_ACTIVITIES_CLASS
STRAVA CREATE_ACTIVITY_HSEARCH_UPD_ALL_JOB CREATE_ACTIVITY_HSEARCH_UPD_ALL_CLASS
STRAVA PROCESS_WEBHOOK_QUEUE_JOB PROCESS_WEBHOOK_QUEUE_CLASS
STRAVA PURGE_API_LOG PURGE_API_LOG_CLASS
STRAVA PURGE_EVENT_QUEUE PURGE_EVENT_QUEUE_CLASS
STRAVA RENEW_STRAVA_TOKENS_JOB RENEW_STRAVA_TOKENS_CLASS
STRAVA UPDATE_STRAVA_ACTIVTY_JOB UPDATE_STRAVA_ACTIVTY_CLASS
@go-faster.co.uk on BlueSky
Posts
