Introduction
Edition based redefinition is a no extra cost feature of the oracle database. It allows to have multiple versions of the same plsql based code (packages, views, triggers, object types, synonyms, …) in the database at the same time.
Code, that starts a scheduled background job, should execute this background job in the same current edition.
Here is how do it using job classes.
TL;DR.
First create a job class that connects to a specific edition via a service name
begin
dbms_scheduler.create_job_class (
job_class_name => 'JCED_DEV$ALPHA',
service => 'DEV_ALPHA'
);
end;
/
Then start the background job using that job class.
v_jobclass_prefix := 'JCED_';
v_jobname := 'myJob';
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'STORED_PROCEDURE',
job_action => 'myPkg.doSomething',
job_class => v_jobclass_prefix ||sys_context('userenv','current_edition_name'),
number_of_arguments => 0,
start_date => sysdate,
enabled => true,
auto_drop => true);
Problem description
This article assumes some basic understanding of the edition based redefinition (EBR) feature of the oracle database.
Scheduled jobs do run in the background in a separate new session. Any new session runs in the default edition of the database, unless it specifies the edition at the time the session is created.
It is possible to switch the edition on demand during a session, but this is not recommended. Chris Saxon misused this possibility for one of his SQL Magic tricks. Not everything that is possible should be used.
Solution
A scheduled job can use a job class. A job class can be set to use a database service. A database service can be set to connect to a specific edition.
When using EBR it is a recommended practice to create a service for each edition and possibly another one for the default edition. Information how to create a service for an edition can by found on one of my older blog entries: working with editions – part 2. Also Oren Nakdimon recently publiced a very nice article about it: using services for exposing new editions .
The following assumes that we have three editions ORA$BASE, TST$BETA and DEV$ALPHA. It also assumes that we have created matching database services ORA_BASE, TST_BETA and DEV_ALPHA and that those services are running.
create the job classes
For each edition we create a job class JCED_<edition_name> (JCED_ORA$BASE, JCED_TST$BETA and JCED_DEV$ALPHA). This can be done with DBA or with the MANAGE SCHEDULER privilege.
Job classes also allow to prioritize between jobs and to connect them to a ressource group.
I’m using a prefix JCED (Job Class EDitioned) to tag the classes. You can use any name you want, but it is advisable to have some common identification for the classes. The name also allows me later to find the correct job class, depending on the current edition of the session.
-- Run as DBA
begin
dbms_scheduler.create_job_class (
job_class_name => 'JCED_ORA$BASE',
service => 'ORA_BASE'
);
end;
/
begin
dbms_scheduler.create_job_class (
job_class_name => 'JCED_TST$BETA',
service => 'TST_BETA'
);
end;
/
begin
dbms_scheduler.create_job_class (
job_class_name => 'JCED_DEV$ALPHA',
service => 'DEV_ALPHA'
);
end;
/
Then we need to make those classes available to the user that will later create the scheduled jobs.
grant execute on sys.JCED_ORA$BASE to mySchema;
grant execute on sys.JCED_TST$BETA to mySchema;
grant execute on sys.JCED_DEV$ALPHA to mySchema;
It is also possible to allow a schema to use any class.
grant execute any class to mySchema;
Job classes do not belong to any schema. They are always created in SYS. Something to keep in mind, for example when exporting/importing schemas to another database.
programatically create a job
The following function creates a background job using the current edition. The job calls a procedure with 3 arguments.
The function is part of some larger package myPackage. It is assumed that the doSomething procedure is also part of the same package.
/* constant declaration section in package body */
g_jobclass_prefix constant varchar2(10) := 'JCED_';
/* modules */
function createBackgroundJob(p_id in number, p_starttime in timestamp) return varchar2
is
co_modul_name CONSTANT VARCHAR2(96) := $$PLSQL_UNIT || '.createBackgroundJob';
v_job_nr binary_Integer;
v_jobname varchar2(100);
begin
v_jobname := 'myJob_'||to_char(p_id);
dbms_scheduler.create_job(job_name => v_jobname,
job_type => 'STORED_PROCEDURE',
job_action => $$plsql_unit || '.doSomething',
job_class => g_jobclass_Prefix||sys_context('userenv','current_edition_name'),
number_of_arguments => 3,
start_date => p_starttime,
enabled => FALSE,
auto_drop => true);
dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>1, argument_value=>p_id);
dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>2, argument_value=>'RUN QUICK');
dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>3, argument_value=>myPackage.g_trace_level);
return v_jobname;
end createBackgroundJob;
The name of the job class is calculated using the name of the current edition by sys_context('userenv','current_edition_name').
start the background job
The createBackgroundJob function only prepares the background job. To run it, we need to call the create function and enable the job afterwards.
declare
job varchar2(128);
begin
job := myPackage.createBackgroundJob(1, localtimestamp);
dbms_scheduler.enable(job);
end;
/
Conclusion
Using job classes is a easy way to start a scheduled job in the same edition that the currently running session is in
Further remarks
Justification
I used this logic to split a plsql heavy task into several worker tasks that could run in parallel. I wanted to make sure that the parallel execution was done using the same edition as the thread that started it.
Restrictions
I did not test if it is possible to run lightweight jobs in a specific edition. There seems to be no obvious restriction that prevents using a job class also for lightweight jobs.
Since job classes belong to sys sometimes they need to be with the schemaname in front :sys.JCED_DEV$ALPHA.
It is not recommened to switch an edition from inside some code, because that code itself is running in a specific edition (the default one). This is very hard to control and to do properly.
Jobs are created with job names in upper case. In some cases we need to make sure to use the upper cased job name, to find/handle the correct job.
Error handling
Error handling needs special care when combining scheduled jobs and editions. For example I make sure that proper instrumentation is in place and that the log entry also includes the edition in which the error happend. In some situations, like if the service is dropped, then the session is still created, but falls back to the default edition. We want to be sure, that we notice any issues arising from such a situation.
svenweller 