Oracle 7.3 - Oracle 23ai 20+ years of experience Version (Released: )

Migrate to Oracle Scheduler Jobs

21 May 2024

Introduction

Hello again and welcome to our new blog. Today we want to show you how KeepTool 16.1 supports you how to migrate your traditional dbms_jobs to more flexible Oracle scheduler jobs. The new scheduler offers additional features and much more control over your jobs.

Traditional Oracle jobs

Many Oracle databases use the traditional jobs managed by procedures provided by the dbms_job package.  Prior to Oracle 10g, dbms_job was the only alternative for implementing database jobs. More or less conveniently, in many databases the traditional job technology survived several database version upgrades.

The following screenshot shows Hora’s overview page for tradtional jobs. Context menu items provide a user interface for dbms_job calls and allow you to comfortably manage these jobs.

Each traditional job is identified by a job number. The “What” column contains a procedure call or anonymous block and identifies the program logic that is executed each time the job runs. The “Interval” column contains an expression to incrementally compute the next run date.

See how it works!

Explore possibilities of KeepTool.

i,g

Scheduler jobs

Starting with Oracle 10g, Oracle introduced a new more flexible concept. It is called scheduler and can be managed by the dbms_scheduler package.

The following hardcopy give you an idea how Hora’s scheduler jobs overview page looks like. There are similar context menu items that encapsulate dbms_scheduler calls and allow you to manage these jobs.

Each scheduler job is identied by the pair of job owner and job name.

Maybe you have noticed the additional creator column. We will deal with the creator separately in the following chapter.

The Action column is just like as superset of the What column of tradional jobs. The Repeat interval column contains an expression to incrementally compute the the next run date.

You can find many publications online that point out the numerous benefits of using scheduler jobs rather than dbms_jobs.

Helpfully, both concepts of database jobs co-exist in your database for a couple of versions . That’s why there is no imminent need to move your dbms_jobs right now. But – as we show you later in this blog, migration is no big thing anymore – when using KeepTool’s migration wizard. Benefits are you instantly can use the new feature the scheduler comes with, and you are prepared for dbms_jobs gets out of support some day.

Starting with Oracle 19c, Oracle launched a first attempt to partially get rid of the traditional dbms_job technology. Whereas dbms_jobs still can be created, Oracle internally manages them by using the dbms_scheduler technology. That’s why for each dbms_job you can find a corresponding scheduler job that is completely managed by Oracle. You can see the job name consists of the “DBMS_JOB$_” prefix and the job number of the traditional job that is implemented by the scheduler job. You can also query the dictionary view scheduler$_dbmsjob_map to find the scheduler job name for an existing dbms_job number and vice versa. See Mike Dietrich’s post for more information.

Avoiding pitfalls

If your database has been upgraded to Oracle 19c by running Oracle’s import tool impdp – usually as SYS – all auto-generated dbms_scheduler jobs may be listed as ‘created by SYS’. In the following hardcopy you can see that the Creator (SYS) differs from the owner (TEST).
Unfortunately, now the jobs run using SYS privileges instead of the job owner’s. That may result in unexpected results of job execution.

Oracle’s recommendation for overcoming this adversity is recreating the jobs as the regular job owner, see DBA_JOBS Running With SYS After 19c Upgrade (Doc ID 2888255.1) for more information.

Maybe this is reason enough to think twice and entirely move your dbms_jobs to scheduler jobs right now. So let’s start with that.

How to migrate to Oracle Scheduler Jobs

Technically, there are at least two approaches for producing dbms_scheduler calls that can replace your dbms_jobs:

  1. You can use Oracle’s dbms_metadata package and generate PL/SQL code from the existing scheduler jobs that are internally used to manage the dbms_jobs.
  2. Use metadata from traditional dba_jobs and build-up PL/SQL code to create adequate scheduler jobs.

We decided using the second approach, because

  • This also works with databases prior to Oracle 19.
  • In some cases, Oracle’s dbms_metadata package ran into an internal unhandled exception when the creator was SYS.

In KeepTool 16.1 you can now find a new context menu item for dbms_jobs. It allows you to create a dbms_scheduler job as a replacement for the selected traditional job.
The menu item has been enabled for multi-selection, i.e. you can create the code for multiple jobs at once.

 

You can find the new menu item right below the “Show DDL” menu item, and it works very similar to that.
The only difference is instead of dbms_job calls it contains dbms_scheduler calls.

The first dbms_scheduler call creates the new job in disabled state.  That allows you to just prepare the new logic and activate it later.

You can remove the comments before the line that contains the call to the “dbms_job.enable()” call to activate the scheduler job right now.
In this case you may also uncomments the “dbms_job.remove()” call to get rid of the traditional job.

The following hardcopy shows Hora’s scheduler overview page after the new scheduler jobs have been created. Jobs that have been auto-created by SYS during the import have been highlighted red, replacement jobs created by Hora are highlighted green.

The next step is removing the traditional jobs because they are not needed any more. You can do it either via script execution or just multi-select them on Hora’s traditional job overview grid and choose the “Remove” context menu item. This will also remove the automatically created scheduler jobs prefixed by “DBMS_JOB_”.

Summary

KeepTool 16.1 helps you to migrate to Oracle Scheduler Jobs. It is as simple as a mouse click to produce a PL/SQL script that creates a scheduler job for each traditional job and optionally, removes the deprecated job. Using our new wizard, the migration to advanced Oracle scheduler technology is a fast and save process, even if you have to change a large number of jobs.