Hello again and welcome to our new blog. Today we want to show you how KeepTool 16.1 heps you how to migrate your traditional dbms_jobs to the more flexible Oracle Scheduler jobs. The new scheduler offers additional features and much more control over your 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 traditional jobs. Context menu items provide a user interface for dbms_job calls and allow you to convenently 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 that incrementally calculates the next run date.
Explore possibilities of KeepTool.
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 screenshot give you an idea of what 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.
ach scheduler job is identified by a job owner and job name pair.
We will discuss the creator separately in the next chapter.
The Action column is a superset of the What column for traditional jobs. The Repeat Interval column contains an expression to incrementally calculate the next run date.
There are many publications on the Internet that point out the many advantages of using scheduler jobs instead of dbms_jobs.
Helpfully, both concepts of database jobs coexist in your database for a few versions. So there’s no immediate need to move your dbms_jobs right now. But – as we will show you later in this blog – migration is not a big deal anymore if you use KeepTool’s migration wizard. The advantages are that you can immediately use the new features of the scheduler and you are prepared for the day when dbms_jobs is no longer supported.
Starting with Oracle 19c, Oracle made a first attempt to partially retire the traditional dbms_jobs technology. While dbms_jobs can still be created, Oracle manages them internally using the dbms_scheduler technology. That’s why for each dbms_job you will find a corresponding scheduler job that is completely managed by Oracle. You can see that 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 scheduler$_dbmsjob_map dictionary view to find the scheduler job name for an existing dbms_job number and vice versa. See Mike Dietrich’s post post for more information.
If your database has been upgraded to Oracle 19c by running Oracle’s import tool impdp – usually as SYS – any automatically generated dbms_scheduler jobs may be listed as ‘created by SYS’. In the following hardcopy you can see that the creator (SYS) is different from the owner (TEST).
Unfortunately, the jobs now run with SYS privileges instead of the job owner’s privileges. This can lead to unexpected job execution results.
Oracle’s recommendation to work around this is to recreate the jobs as the regular job owner, see DBA_JOBS Running With SYS After 19c Upgrade (Doc ID 2888255.1) for more information.
Perhaps this is reason enough to think twice and move all your dbms_jobs to scheduler jobs now. So let’s get started.
Technically, there are at least two approaches for producing dbms_scheduler calls to replace your dbms_jobs:
We chose the second approach because
It allows you to create a dbms_scheduler job to replace the selected traditional job.
The menu item is multi-selectable, i.e. you can create the code for multiple jobs at once.
You can find the new menu item just below the “Show DDL” menu item, and it works very similarly.
The only difference is that it contains dbms_scheduler calls instead of dbms_job calls.
The first dbms_scheduler call creates the new job in a disabled state. This allows you to simply prepare the new logic and activate it later.
You can remove the comments before the line that contains the call to “dbms_job.enable()” to enable the Job Scheduler job now.
In this case, you can also uncomment the “dbms_job.remove()” call to get rid of the traditional job.
The following screenshot shows Hora’s scheduler overview page after the new scheduler jobs have been created. The jobs created automatically by SYS during the import are highlighted in red, the replacement jobs created by Hora are highlighted in green.
You can do this either by running a script or by simply selecting them in Hora’s traditional job overview grid and choosing the “Remove” context menu item. This will also remove the automatically created scheduler jobs with the prefix “DBMS_JOB_”.
It is as easy as a mouse click to create a PL/SQL script that creates a scheduler job for each traditional job and optionally removes the obsolete job. Using our new wizard, migrating to advanced Oracle Scheduler technology is a quick and safe process, even if you have a large number of jobs to change.