Oracle 19c注意事项： DBMS |
您所在的位置：网站首页 › oracle数据库job创建 › Oracle 19c注意事项： DBMS |
Oracle 19c注意事项： DBMS
|
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 — 打赏
 微信扫一扫，打赏作者吧～
|
【本文地址】
今日新闻 |
推荐新闻 |