ANBOB™

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 —

打赏

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