This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.
Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:
1) What about new jobs submitted via the old API after the upgrade?
For comparison, here’s what you see currently in 18c – DBMS_JOB is quite separate from the scheduler.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL> declare
2 j int;
3 begin
4 dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select job, what from user_jobs;
JOB WHAT
---------- --------------------------------------------------
181 begin dbms_session.sleep(60); end;
SQL> select job_name, job_action from user_scheduler_jobs;
no rows selected
Now here’s the same in 19c
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> declare
2 j int;
3 begin
4 dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select job, what from user_jobs;
JOB WHAT
---------- --------------------------------------------------
22 begin dbms_session.sleep(60); end;
SQL> select job_name, job_action from user_scheduler_jobs;
JOB_NAME JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_22 begin dbms_session.sleep(60); end;
SQL> select * from user_scheduler_jobs
2 @pr
==============================
JOB_NAME : DBMS_JOB$_22
JOB_SUBNAME :
JOB_STYLE : REGULAR
JOB_CREATOR : MCDONAC
CLIENT_ID :
GLOBAL_UID :
PROGRAM_OWNER :
PROGRAM_NAME :
JOB_TYPE : PLSQL_BLOCK
JOB_ACTION : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS : 0
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : PLSQL
START_DATE : 26-MAY-19 07.12.47.000000 PM +08:00
REPEAT_INTERVAL : sysdate+1
EVENT_QUEUE_OWNER :
EVENT_QUEUE_NAME :
EVENT_QUEUE_AGENT :
EVENT_CONDITION :
EVENT_RULE :
FILE_WATCHER_OWNER :
FILE_WATCHER_NAME :
END_DATE :
JOB_CLASS : DEFAULT_JOB_CLASS
ENABLED : TRUE
AUTO_DROP : TRUE
RESTART_ON_RECOVERY : FALSE
RESTART_ON_FAILURE : FALSE
STATE : SCHEDULED
JOB_PRIORITY : 3
RUN_COUNT : 0
UPTIME_RUN_COUNT :
MAX_RUNS :
FAILURE_COUNT : 0
UPTIME_FAILURE_COUNT :
MAX_FAILURES :
RETRY_COUNT : 0
LAST_START_DATE :
LAST_RUN_DURATION :
NEXT_RUN_DATE : 27-MAY-19 07.12.47.000000 PM -07:00
SCHEDULE_LIMIT :
MAX_RUN_DURATION :
LOGGING_LEVEL : OFF
STORE_OUTPUT : TRUE
STOP_ON_WINDOW_CLOSE : FALSE
INSTANCE_STICKINESS : TRUE
RAISE_EVENTS :
SYSTEM : FALSE
JOB_WEIGHT : 1
NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'...
SOURCE :
NUMBER_OF_DESTINATIONS : 1
DESTINATION_OWNER :
DESTINATION :
CREDENTIAL_OWNER :
CREDENTIAL_NAME :
INSTANCE_ID :
DEFERRED_DROP : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS :
FLAGS : 9007199254872624
RESTARTABLE : FALSE
HAS_CONSTRAINTS : FALSE
CONNECT_CREDENTIAL_OWNER :
CONNECT_CREDENTIAL_NAME :
FAIL_ON_SCRIPT_ERROR : FALSE
PL/SQL procedure successfully completed.
You can see that it will be enabled by default and is classed as a regular job. Even if you submit a one-off job, it will still be classed as regular not lightweight.
SQL> declare
2 j int;
3 begin
4 dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select job, what from user_jobs;
JOB WHAT
---------- --------------------------------------------------
25 begin dbms_session.sleep(60); end;
SQL> select * from user_scheduler_jobs
2 @pr
==============================
JOB_NAME : DBMS_JOB$_25
JOB_SUBNAME :
JOB_STYLE : REGULAR
JOB_CREATOR : MCDONAC
CLIENT_ID :
GLOBAL_UID :
PROGRAM_OWNER :
PROGRAM_NAME :
JOB_TYPE : PLSQL_BLOCK
JOB_ACTION : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS : 0
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : ONCE
START_DATE : 26-MAY-19 08.37.09.000000 PM +08:00
REPEAT_INTERVAL :
EVENT_QUEUE_OWNER :
EVENT_QUEUE_NAME :
EVENT_QUEUE_AGENT :
EVENT_CONDITION :
EVENT_RULE :
FILE_WATCHER_OWNER :
FILE_WATCHER_NAME :
END_DATE :
JOB_CLASS : DEFAULT_JOB_CLASS
ENABLED : TRUE
AUTO_DROP : TRUE
RESTART_ON_RECOVERY : FALSE
RESTART_ON_FAILURE : FALSE
STATE : SCHEDULED
JOB_PRIORITY : 3
RUN_COUNT : 0
UPTIME_RUN_COUNT :
MAX_RUNS :
FAILURE_COUNT : 0
UPTIME_FAILURE_COUNT :
MAX_FAILURES :
RETRY_COUNT : 0
LAST_START_DATE :
LAST_RUN_DURATION :
NEXT_RUN_DATE : 27-MAY-19 11.37.09.268652 AM +08:00
SCHEDULE_LIMIT :
MAX_RUN_DURATION :
LOGGING_LEVEL : OFF
STORE_OUTPUT : TRUE
STOP_ON_WINDOW_CLOSE : FALSE
INSTANCE_STICKINESS : TRUE
RAISE_EVENTS :
SYSTEM : FALSE
JOB_WEIGHT : 1
NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENC
NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_L
NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_T
NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINA
NLS_NCHAR_CONV_EXCP='FALSE'
SOURCE :
NUMBER_OF_DESTINATIONS : 1
DESTINATION_OWNER :
DESTINATION :
CREDENTIAL_OWNER :
CREDENTIAL_NAME :
INSTANCE_ID :
DEFERRED_DROP : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS :
FLAGS : 9007199254880304
RESTARTABLE : FALSE
HAS_CONSTRAINTS : FALSE
CONNECT_CREDENTIAL_OWNER :
CONNECT_CREDENTIAL_NAME :
FAIL_ON_SCRIPT_ERROR : FALSE
PL/SQL procedure successfully completed.
Important Note: There is one critical thing you need to be aware of with this change. DBMS_JOB is an “old-school” public API, hence anyone and everyone pretty much had access to it. Anyone familiar with DBMS_SCHEDULER will know that the components within it are true database objects, which can be protected with privileges. So when you upgrade to 19c, to ensure that you do not get nasty surprises, users that are using DBMS_JOB will need the CREATE JOB privilege otherwise their previous ability to submit jobs will disappear. For example:
SQL> conn scott/tiger@db192_pdb1
Connected.
SQL> declare
2 j int;
3 begin
4 dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
5 end;
6 /
declare
*
ERROR at line 1:
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
2) What about the cherished transactional nature of DBMS_JOB?
If the old style jobs are now scheduler jobs, do we lose the transactional element of DBMS_JOB? Nope. Even though we will create a paired scheduler entry, DBMS_JOB is still transactional (which I love!).
SQL> declare
2 j int;
3 begin
4 dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select job, what from user_jobs;
JOB WHAT
---------- --------------------------------------------------
21 begin dbms_session.sleep(60); end;
SQL> select job_name, job_action from user_scheduler_jobs;
JOB_NAME JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_21 begin dbms_session.sleep(60); end;
SQL> roll;
Rollback complete.
SQL> select job, what from user_jobs;
no rows selected
SQL> select job_name, job_action from user_scheduler_jobs;
no rows selected
SQL>
If you love that transactional capability too, then make sure to visit the database ideas page to vote up this idea. I have no issue with DBMS_SCHEDULER doing commits by default, but it would be cool if (say) for lightweight jobs we had an option to choose whether we commit or not.
Learning is not a spectator sport 
Got some thoughts? Leave a comment