Tag: dbms_scheduler

A couple of people have already written about a new feature in oracle 19c, which converts jobs created using DBMS_JOB into DBMS_SCHEDULER jobs.

• DBMS_JOB – Behavior Change in Oracle 19c during upgrade (Mike Dietrich)
• DBMS_JOB – the joy of transactions (Connor Mcdonald)

I finally got round to writing up my notes about it here.

• DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The conversion comes with rather interesting consequences.

The scheduler side of things is tighter. As Connor pointed out you now need the CREATE JOB privilege to use the DBMS_JOB package. That’s nice, but isn’t that the opposite of the title of this post? Yes, but…

The problem is the re-implementation of materialized refresh groups using the DBMS_REFRESH package didn’t seem to follow the same approach. You can create a refresh group, which creates a DBMS_SCHEDULER job, without needing the CREATE_JOB privilege. Once you own a job, you can amend it, which means there is now a method to create DBMS_SCHEDULER jobs without needing the CREATE JOB privilege. Doh! You can see an example of it in 19.3 here.

Thoughts:

• It’s clearly a bug, and I’m sure it will be picked up in a future release.
• Although it seems pretty bad, remember the DBMS_REFRESH and DBMS_JOB packages are available by default and in previous releases you didn’t need the CREATE JOB privilege to them.
• If this is a problem you can revoke execute on DBMS_REFRESH from PUBLIC, like you may have been doing for DBMS_JOB already.

Cheers

Tim…

PS. SR raised.

SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege.

PPS. This is now Bug 30357828 and is being worked on.