oracle中job的retry次数,Oracle 19c注意事项： DBMS_JOB 行为变化

DBMS_SCHEDULER 是一种新的JOB调度形式，提供了功能更加强大和跟踪的功能，说是新是相对DBMS_JOB, schedure从10G时引入已经十多年， 用于替换DBMS_JOB, 如果你升级19c 时原来的库有dbms_job对象，会在preupgrade.jar中提示Warning JOB_TABLE_INTEGERITY.

不用担心，这只是检查的一种形式，从12c 开始就已经dbms_jobs是deprecated，但是一直可以使用向前兼容，注意从ORA 19C开始 DBMS_JOB总是以DBMS_SCHEDULER的形式创建，并且dbms_job仍然有效只是多了一层对应关系。 dbms_job也只是调用了dbms_scheduler.

下面创建一个例子

SQL> alter session set container=pdb1;

Session altered.

SQL> create or replace procedure do_null

2 is

3 begin

4 null;

5 -- www.anbob.com;

6 end;

7 /

Procedure created.

SQL> var jobn number

SQL> @46on 12

Session altered.

SQL> exec dbms_job.submit(:jobn,'do_null;',sysdate,'sysdate+1/24/60');

PL/SQL procedure successfully completed.

SQL> @46off

— trace file

$ grep -i insert tracefile|grep -v access

INSERT INTO SYS.SCHEDULER$_DBMSJOB_MAP VALUES (:B3 , :B2 , :B1 )

insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3, signature, spare7, spare8, spare9, dflcollid, creappid, creverid, modappid, modverid, crepatchid, modpatchid) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18, :19,:20,:21,:22,decode(:23,0,null,:23),:24,:25,:26,:27,:28,:29)

insert into sys.scheduler$_job (program_oid, schedule_expr, schedule_id, queue_owner, queue_name, queue_agent, event_rule, mxdur_msgid, last_enabled_time, class_oid, next_run_date, last_start_date, last_end_date, retry_count, run_count, failure_count, running_instance, running_slave, flags, job_status, creator, client_id, guid, char_env, start_date, end_date, instance_id, fw_name, fw_oid, destination, credential_name, credential_owner, credential_oid, dest_oid, job_dest_id, run_invoker, connect_credential_name, connect_credential_owner, connect_credential_oid, program_action, schedule_limit, priority, job_weight, number_of_args, max_runs, max_failures, max_run_duration, comments, user_callback, user_callback_ctx, nls_env, source, env, database_role, owner_udn, dist_flags, pdb_id, obj#) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58)

Note:

注意使用dbms_job创建job

1, 在SYS.SCHEDULER$_DBMSJOB_MAP映射一个job和scheduler的关系

2， obj$ 增加一个对象

3, sys.scheduler$_job 增加一个JOB信息。

SQL> @st SYS.SCHEDULER$_DBMSJOB_MAP

DBMS_JOB_NUMBER JOB_OWNER JOB_NAME

--------------- ------------------------------ ------------------------------

2 SYS DBMS_JOB$_2

SQL> @jobs

-- dbms_jobs

JOB WHAT LAST_DATE NEXT_DATE INTERVAL FAILURES B

---------- -------------- --------------- -------------------------------------- ---------------------------------------- ---------- -

2 do_null; 12-AUG-19 10.14.40.972533 PM +08:00 sysdate+1/24/60 0 N

-- dba_scheduler_jobs

JOB_NAME PROGRAM_NAME JOBS_STATE START_DATE NEXT_RUN_DATE ENABL

---------------------------------------- ---------------------------------------- --------------- ---------------- ---------------- -----

DBMS_JOB$_2 SCHEDULED 2019-08-12 22:14 2019-08-12 22:14 TRUE

SQL> select text_vc from dba_viewS where view_name='DBA_JOBS';

TEXT_VC

--------------------------------------------------------------------------------

select

m.dbms_job_number JOB, j.creator LOG_USER, u.name PRIV_USER,

u.name SCHEMA_USER,

j.last_start_date LAST_DATE,

substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8) LAST_SEC,

DECODE(BITAND(j.job_status,2), 2, j.last_start_date, NULL) THIS_DATE,

DECODE(BITAND(j.job_status,2), 2,

substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8), NULL) THIS_SEC,

j.next_run_date NEXT_DATE,

substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_SEC,

(CASE WHEN j.last_end_date>j.last_start_date THEN

extract(day from (j.last_end_date-j.last_start_date)*86400) ELSE 0 END)

TOTAL_TIME, -- Scheduler does not track total time

DECODE(BITAND(j.job_status,1),0,'Y','N') BROKEN,

DECODE(BITAND(j.flags,1024+4096+134217728),

0, j.schedule_expr, NULL) INTERVAL,

j.failure_count FAILURES, j.program_action WHAT,

j.nls_env NLS_ENV, j.env MISC_ENV, NVL(j.instance_id, 0) INSTANCE

from

sys.scheduler$_dbmsjob_map m

left outer join sys.obj$ o on (o.name = m.job_name)

left outer join sys.user$ u on (u.name = m.job_owner)

left outer join sys.scheduler$_job j on (j.obj# = o.obj#)

where

o.owner# = u.user#

Note：

注意dba_jobs view中仍然可以查到JOB，同样在dba_scheduler_jobs中也可以查到对应的记录，并且job_name 为“DBMS_JOB$_”和jobnum号。 同时在dba_jobs的定义已经使用了sys.scheduler$_job，和scheduler$_dbmsjob_map关连显示。 注意dba_views 的text也变为了text_vc。

— enjoy —

打赏

微信扫一扫，打赏作者吧～