Actually I missed to blog about this change but luckily a colleague did ask a question the other week about the migration of jobs when you upgrade to Oracle 19c. Let me shed some light on DBMS_JOB – Behavior Change in Oracle 19c.
DBMS_JOB vs DBMS_SCHEDULER
Back in the 10g days, we introduced a new interface to run and monitor jobs in the database: The scheduler. The scheduler with
DBMS_SCHEDULER is way more powerful than the old job interface. And we use it internally a lot as well. But as things appear in the real world. the introduction of a better interface doesn’t lead to rapid migrations.
In addition, in case you haven’t see this, we deprecated the
DBMS_JOB interface with Oracle Database 220.127.116.11.
Conversion during the upgrade
With the upgrade to Oracle Database 19c there will be a conversion of jobs under the hood of the old
DBMS_JOB interface. No worries – relax!
What happens? And what does that mean for your jobs in
First of all, you can’t prevent or skip the migration under the scheduler’s control. But there is also no reason to do so. During the preupgrade check you may see the following warning:
No panic. There are three clarifications to mention:
- During the 19c upgrade for each job in
DBMS_JOBa corresponding entry will be created with
- The old
DBMS_JOBinterface still works. But using it will always create a corresponding entry in the scheduler
- The check in preupgrade.jar is only checking for inconsistencies or any issues
What this means is: You can still use
DBMS_JOB but under the cover we are using
DBMS_SCHEDULER. The internal procedures have been changed. Your calls will work the same way, but
DBMS_JOB is now a legacy interface to the
A simple testcase
As I always like to try out things, here’s a very simple testcase creating a job with DBMS_JOB in Oracle 18.104.22.168. Then I upgrade the database – and check afterwards.
connect system/oracle drop user crc cascade; grant connect, resource, dba to crc identified by crc; connect crc/crc alter session set nls_language = american; alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS'; create table jobtest ( a date); var jobno number var jobstring VARCHAR2(2000) -- setup of a simple job inserting a value into a test table every 3 minutes: execute dbms_job.submit(:jobno, 'BEGIN insert into jobtest values (sysdate); commit; END;', sysdate, interval=> 'SYSDATE + 3/1440'); SELECT job,what,next_date,interval FROM dba_jobs WHERE job = :jobno;
This is what I have in my database before the upgrade – and of course this job is not visible in
JOB WHAT NEXT_DATE INTERVAL --- ---------------------------------------------------------- -------------------- ------------------ 1 BEGIN insert into jobtest values (sysdate); commit; END; 24-05-19 09:36:32 SYSDATE + 3/1440
But after the upgrade, the informatin in
DBA_JOBS is still present. And in addition, in
DBA_SCHEDULER_JOBS I have a new entry:
JOB_NAME -------------------- DBMS_JOB$_1
In DBMS_JOB, jobs have no name. Instead they get identified by an ID. For mapping purposes a new dictionary table
scheduler$_dbmsjob_map has been defined.
This works transparently during upgrade and downgrade. And finally, this isn’t really a behavior change as everything happens underneath the covers.
More information and Links?
- Connor McDonald’s blog post: DBMS_JOB – the joy of transactions (May 28, 2019)
- Scheduler Concepts – Oracle 19c Admin Guide
- Database Upgrade Guide 19c – Support for DBMS_JOB
- MOS Note: 2117140.1 – An Example to Convert from DBMS_JOB Jobs to DBMS_SCHEDULER Jobs
- MOS Note: 807159.1 – Sample Code and Select Statements to Build Simple Testcases for Scheduler / Jobs
- DBMS_JOB Deprecation Note in Oracle 22.214.171.124
- What does deprecated mean, what does desupported mean?
- MOS Note: 2380601.1 – Database Preupgrade tool check list