A while ago I blogged already about this behavior change for DBMS_JOB in Oracle Database 19c. Many of you are aware of this. But today I learned something new about it. Kudos to Alexey Makhmutov for bringing this to my attention. There is a very important fix you will need on top in 19c to avoid performance issues. If you rely on jobs with the old job interface, take note: DBMS_JOB one-off patch needed for Oracle 19.3.0 – 19.7.0
What is the problem?
As you can read from there is a internal translation between DBMS_JOB and DBMS_SCHEDULER. And so far, I’m not aware of issues with this. But an internal check happens for duplicate names in
DBMS_JOB_MAP table. And this check could lead to a hang. You can reproduce this quite easily by creating a job with DBMS_JOB in one session, but do not commit. Then create another job in second session, and it will hang.
I’m posting this here on purpose as I work with two very large customers who still rely on DBMS_JOB. And I guess, there may be many more out there.
What is the solution?
[See the comments below: The fix is included from 19.9.0 (October 2020) onwards]
You need a patch. As described in MOS Note: 2645984.1 – Delete On SYS.SCHEDULER$_DBMSJOB_MAP Causing Row Locks, you need to apply Patch 30835853 to your Oracle 19c home. This issue hopefully will be fixed in a future RU – my colleagues are taking the necessary steps already.
At the moment, I see one-offs on top of all RUs until 19.7.0, but not for all platforms. Especially the one-off for Linux for 19.7.0 is missing right now. This may change within the next days. If you need it, please request it via an SR if you can’t find it here.
More Information and Links
- Original blog post: Behavior Change in DBMS_JOB in Oracle 19c
- MOS Note: 2645984.1 – Delete On SYS.SCHEDULER$_DBMSJOB_MAP Causing Row Locks
- Patch 30835853