📋Steps in this guide1/5

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

oracle 19cconfigurationintermediate

by OracleDba

20 views

Create a Job Using DBMS_JOB

In Oracle 19c jobs created using the package are implemented as jobs, as demonstrated below. We can see from the output below we don't have any jobs for this user. We create a job using the procedure, but we are not going to issue a statement. We can see the job is listed in the and views. Notice the of "DBMS_JOB$_?" for the job that has been generated.

Code/Command (click line numbers to comment):

conn test/test@pdb1

column what format a30

select job, what from user_jobs;

0 rows selected.

SQL>


column job_name format a30
column job_action format a30

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

declare
  l_job  pls_integer;
begin
  dbms_job.submit (
    job       => l_job,
    what      => 'begin null; end;',
    next_date => trunc(sysdate)+1,
    interval  => 'trunc(sysdate)+1'
  );
end;
/

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; end;

1 row selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

Transactional Jobs

One of the reasons people still use the package is it allows you to create jobs that are part of a bigger transaction. If a failure causes an exception, all the current work along with the jobs defined as part of it can be rolled back. We can demonstrate this using the job created above. Remember, we didn't issue a , so the job is not visible from another session connected to the same user. Without closing the original session, open a new connection and check for the jobs. Now return to the original session and the jobs are still visible. Issue a , and the job definition will be removed. As a result, the package can still be used to create transactional jobs, that are implemented using the scheduler. This also provides backwards compatibility.

Code/Command (click line numbers to comment):

conn test/test@pdb1

select job, what from user_jobs;

0 rows selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; end;

1 row selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

rollback;

select job, what from user_jobs;

0 rows selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

Materialized View Refresh Groups

Up to and including Oracle 18c, materialized view refresh groups were implemented using the kernel APIs exposed by the old package. In Oracle 19c things look a little different. Create a table, materialized and refresh group including that materialized view. We don't see a job in the view, but we do see one in the view. But this job is transactional, in that a will remove the job, along with the refresh group definition. It would appear the refresh group functionality has been re-implemented using the kernel APIs that sit under the package, but without the implicit commit. Similar to the way the interface has been re-implemented. This is not 100% backwards compatible, as the associated job is not visible in the view. If you have any functionality that relies on the link between the refresh groups and the old scheduler, it will need revisiting. I can't imagine that will be a problem for most people. You can clean up the test table and materialized view using these commands.

Code/Command (click line numbers to comment):

create table t1 (id number);

create materialized view t1_mv
refresh force
on demand
as
select * from t1;

begin
   dbms_refresh.make(
     name                 => 'MINUTE_REFRESH',
     list                 => '',
     next_date            => sysdate,
     interval             => '/*1:mins*/ sysdate + 1/(60*24)',
     implicit_destroy     => false,
     lax                  => false,
     job                  => 0,
     rollback_seg         => null,
     push_deferred_rpc    => true,
     refresh_after_errors => true,
     purge_option         => null,
     parallelism          => null,
     heap_size            => null);
end;
/

begin
   dbms_refresh.add(
     name => 'MINUTE_REFRESH',
     list => 'T1_MV',
     lax  => true);
end;
/

select job, what from user_jobs;

0 rows selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
MV_RF$J_0_S_210                dbms_refresh.refresh('"TEST"."
                               MINUTE_REFRESH"');


1 row selected.

SQL>

rollback;


select job, what from user_jobs;

0 rows selected.

SQL>


select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

drop materialized view t1_mv;
drop table t1 purge;

Security : The CREATE JOB Privilege is Required?

At first glance the loophole discussed here sounds really bad, but remember that even in Oracle 18c, any user connected to the database could create a job using the interface, so this loophole is no worse than what came before. It just breaks the security. As Connor McDonald pointed out, the conversion means users require the privilege to allow them to create jobs using the package, where previously they didn't. We can see this if we create a user with just the privilege and attempt to create a job. There is a loophole caused by the refresh group implementation. If we repeat the previous refresh group example, we can see we are able to create a job without the privilege. That in itself is not devastating because it's for a very specific purpose, but most of Oracle's security is based on you being able to do whatever you want with objects you already own, so what happens if we try to change the attributes? So we can create a job using the package, then alter it to suit our purpose, giving us the ability to create a job without the need for the privilege. It would appear the re-implementation of the package has not followed the same security rules as that used by the other scheduler implementations. I'm sure this will get fixed in a future release. Until this issue is resolved, you should probably revoke on the package from , as you may already do for the package. Note. I raised this issue as "SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege". This is now Bug 30357828 and is being worked on.

Code/Command (click line numbers to comment):

create user test2 identified by test2;
grant create session to test2;


conn test2/test2@pdb1

declare
  l_job  pls_integer;
begin
  dbms_job.submit (
    job       => l_job,
    what      => 'begin null; end;',
    next_date => trunc(sysdate)+1,
    interval  => 'trunc(sysdate)+1'
  );
end;
/

Error report -
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 4
27486. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a scheduler operation without the
           required privileges.
*Action:   Ask a sufficiently privileged user to perform the requested
           operation, or grant the required privileges to the proper user(s).
SQL>

begin
   dbms_refresh.make(
     name                 => 'MINUTE_REFRESH',
     list                 => '',
     next_date            => sysdate,
     interval             => '/*1:mins*/ sysdate + 1/(60*24)',
     implicit_destroy     => false,
     lax                  => false,
     job                  => 0,
     rollback_seg         => null,
     push_deferred_rpc    => true,
     refresh_after_errors => true,
     purge_option         => null,
     parallelism          => null,
     heap_size            => null);
end;
/


select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
MV_RF$J_0_S_242                dbms_refresh.refresh('"TEST2".
                               "MINUTE_REFRESH"');


1 row selected.

SQL>

begin
  dbms_scheduler.set_attribute (
    name      => 'MV_RF$J_0_S_242',
    attribute => 'job_action',
    value     => 'begin null; end;'
  );
end;
/


SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
MV_RF$J_0_S_242                begin null; end;

1 row selected.

SQL>

DBA Hub

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

Create a Job Using DBMS_JOB

Code/Command (click line numbers to comment):

Transactional Jobs

Code/Command (click line numbers to comment):

Materialized View Refresh Groups

Code/Command (click line numbers to comment):

Security : The CREATE JOB Privilege is Required?

Code/Command (click line numbers to comment):

Miscellaneous

Comments (0)