DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

DBMS_JOB and DBMS_SCHEDULER Integration in Oracle 19c

#oracle #database #linux #devops

Before Oracle 10g, the DBMS_JOB package was used to create jobs in the database environment. In version 10g, Oracle introduced an improvement by providing the DBMS_SCHEDULER package, which addressed many of DBMS_JOB’s limitations and effectively reduced its usage.

However, users can still use DBMS_JOB to create new jobs or manage older ones (created in earlier versions), and even Oracle itself still relies on this package for scheduling certain internal operations.

Example: Materialized View Refresh

For example, when creating a Materialized View (MV), if a refresh interval and start time are defined for automatic updates, Oracle will create a DBMS_JOB to handle the refresh:

SQL> CREATE MATERIALIZED VIEW "USEF"."MVTEST"
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('10-Jul-2019 02:08:26 PM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 1
ENABLE QUERY REWRITE
AS select * from jj;
Enter fullscreen mode Exit fullscreen mode

After running the above command, job number 7603 is created:

SQL> select job, schema_user, interval, what from dba_jobs where schema_user='USEF';
Enter fullscreen mode Exit fullscreen mode

Behavior Change in Oracle 18c

Starting from Oracle 18c, this behavior changed. Oracle no longer uses DBMS_JOB to refresh materialized views:

SQL> CREATE MATERIALIZED VIEW "USEF"."MVTEST"
...
SQL> select job, schema_user, interval, what from dba_jobs where schema_user='USEF';
no rows selected

SQL> select owner, job_name, job_action, repeat_interval from dba_scheduler_jobs where owner='USEF';
Enter fullscreen mode Exit fullscreen mode


Further Changes in Oracle 19c

In Oracle 19c, additional behavior changes were introduced regarding DBMS_JOB. Let’s examine these differences in detail.

We’ll create a job using DBMS_JOB in an Oracle 18c database, then upgrade the database to 19c and observe what changes occur.
Creating a Job in Oracle 18c
We create a job that executes the myproc1 procedure every minute:
— 18c

SQL> var job VARCHAR2(2000)
SQL> begin
  sys.dbms_job.submit(job => :job, what => 'myproc1;', interval => 'sysdate+1/24/60');
  commit;
end;
/
PL/SQL procedure successfully completed
job

———

2
Enter fullscreen mode Exit fullscreen mode

You can check its details:

select job, schema_user, interval, what from dba_jobs;
Enter fullscreen mode Exit fullscreen mode


In this database, the user USEF has only this job and no DBMS_SCHEDULER jobs:

select count(*) from dba_scheduler_jobs where owner='USEF';
0
Enter fullscreen mode Exit fullscreen mode

After Upgrading to Oracle 19c
After upgrading the database to version 19c, we recheck the job status:

select job, schema_user, interval, what from dba_jobs;
Enter fullscreen mode Exit fullscreen mode

The DBA_JOBS view still shows job number 2.
However, a new discovery appears when querying DBA_SCHEDULER_JOBS:

select count(*) from dba_scheduler_jobs where owner='USEF';
1
Enter fullscreen mode Exit fullscreen mode

Before the upgrade, the user USEF had no scheduler jobs, but after the upgrade, one scheduler job now exists!

select owner, job_name, job_action, repeat_interval from dba_scheduler_jobs where owner='USEF';
Enter fullscreen mode Exit fullscreen mode


Indeed, a corresponding scheduler job exists that runs the myproc1 procedure on the same interval as the DBMS_JOB one.

Comparing Job Timings

Now, let’s compare the next and last run times between the two views:

select s.last_date, s.next_date from dba_jobs s;
Enter fullscreen mode Exit fullscreen mode 


select p.last_start_date, p.next_run_date from dba_scheduler_jobs p where p.job_name='DBMS_JOB$_2';
Enter fullscreen mode Exit fullscreen mode

Both queries return identical dates.

Moreover, the job can be executed through both packages:

SQL> exec dbms_scheduler.run_job(job_name => ‘DBMS_JOB$_2’);
PL/SQL procedure successfully completed

SQL> exec dbms_job.run(job => 2);
PL/SQL procedure successfully completed
Enter fullscreen mode Exit fullscreen mode

Both commands complete successfully.

Synchronization Between Packages

If we change the WHAT clause (the action of the job) through DBMS_JOB, it automatically updates the JOB_ACTION in the scheduler as well:

SQL> exec dbms_job.what(job => 2,what => ‘proc5’);
PL/SQL procedure successfully completed

SQL> commit;
Commit complete

select (select job_action from dba_scheduler_jobs p where p.job_name=’DBMS_JOB$_2′) scheduler, (select what from dba_jobs s) dba_jobs from dual;
Enter fullscreen mode Exit fullscreen mode

As you can see, for every job created using the DBMS_JOB package, an equivalent job is also created through the DBMS_SCHEDULER package, and the management of this job can be performed using either of these two packages.

Moreover, jobs newly created in Oracle 19c (via the DBMS_JOB package) follow the same structure:

SQL> var job VARCHAR2(2000)
SQL> begin 
  sys.dbms_job.submit(job => :job, what => 'proc19c;', interval => 'sysdate+10');
  commit;
end;
/
PL/SQL procedure successfully completed

job
———
22
Enter fullscreen mode Exit fullscreen mode 
select 
  (select job_name from dba_scheduler_jobs p where p.job_name like '%22') scheduler, 
  (select job from dba_jobs s where job=22) dba_jobs 
from dual;
Enter fullscreen mode Exit fullscreen mode

In version 19c, a new data dictionary table named SCHEDULER$_DBMSJOB_MAP has been added.
Through this table, you can identify which DBMS_JOB is mapped to which DBMS_SCHEDULER job:

select * from sys.scheduler$_dbmsjob_map;
Enter fullscreen mode Exit fullscreen mode

This table also displays information about deleted jobs.

Top comments (0)