19c DBMS_JOB 变化
Oracle 19c注意事项： DBMS_JOB 变化
DBMS_SCHEDULER 是一种新的JOB调度形式，提供了功能更加强大和跟踪的功能，说是新是相对DBMS_JOB, schedure从10G时引入已经十多年， 用于替换DBMS_JOB，从12c 开始就已经dbms_jobs是deprecated，但是一直可以使用向前兼容，
注意：从ORACLE 19C开始 DBMS_JOB总是以DBMS_SCHEDULER的形式创建，并且dbms_job仍然有效只是多了一层对应关系。
dbms_job也只是调用了dbms_scheduler.
官方文档描述：
Oracle Scheduler replaces the DBMS_JOB package. Although DBMS_JOB is still supported for backward compatibility, Oracle strongly recommends that you switch from DBMS_JOB to Oracle Scheduler.
In upgrades of Oracle Database 19c and later releases, if the upgrade can recreate existing DBMS_JOB jobs using DBMS_SCHEDULER, then for backward compatibility, after the upgrade, DBMS_JOB continues to act as a legacy interface to the DBMS_SCHEDULER job.
1. 不同版本测试
11g 环境下测试
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
-- 创建dbms job
SQL> CREATE TABLE SXC.TJ_ARCH (time varchar2(20),id int,cnt int);
Table created.
SQL> DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'insert into SXC.TJ_ARCH
select *
from (select to_char(first_time, ''yyyy-mm-dd hh24'') FirstTime,
THREAD#,
count(*)
from v$log_history
where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'')
group by to_char(first_time, ''yyyy-mm-dd hh24''), THREAD#);'
,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
PL/SQL procedure successfully completed.
-- 检查dbms job
SQL> set line222
SQL> col LOG_USER for a10
SQL> col PRIV_USER for a10
SQL> col SCHEMA_USER for a20
SQL> select job,LOG_USER,PRIV_USER ,SCHEMA_USER from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER
---------- ---------- ---------- --------------------
84 SXC SXC SXC
-- 检查scheduler job
SQL> col owner for a20
SQL> col JOB_NAME for a40
SQL> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='SXC';
no rows selected
结论： 只创建了dbms job，并未创建scheduler job.
19c 环境下测试
-- 因为是cdb架构，我们选择同tns的方式连接到其中一个pdb
[oracle@ora19c ~]$ sqlplus sxc/sxc@ORCLPDB1;
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 7 23:13:33 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Jul 07 2022 23:11:33 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from v$version;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
---------------------------------------------------------------------------------- -------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
Version 19.3.0.0.0
-- 创建job
SQL> CREATE TABLE SXC.TJ_ARCH (time varchar2(20),id int,cnt int);
Table created.
SQL> DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'insert into SXC.TJ_ARCH
select *
from (select to_char(first_time, ''yyyy-mm-dd hh24'') FirstTime,
THREAD#,
count(*)
from v$log_history
where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'')
group by to_char(first_time, ''yyyy-mm-dd hh24''), THREAD#);'
,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
PL/SQL procedure successfully completed.
-- 检查dbms job
SQL> set line222
SQL> col LOG_USER for a10
SQL> col PRIV_USER for a10
SQL> col SCHEMA_USER for a20
SQL> select job,LOG_USER,PRIV_USER ,SCHEMA_USER from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER
---------- ---------- ---------- --------------------
22 SXC SXC SXC
-- 检查scheduler job
SQL> col owner for a20
SQL> col JOB_NAME for a40
SQL> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='SXC';
OWNER JOB_NAME STATE SUBSTR(JOB_ACTION,1,50)
-------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SXC DBMS_JOB$_22 SCHEDULED insert into SXC.TJ_ARCH select * from (selec
结论： 即创建了dbms job，也创建scheduler job.
注意： dba_jobs view中仍然可以查到 job，同样在dba_scheduler_jobs中也可以查到对应的记录，并且job_name 为 “DBMS_JOB$” 和job num号。
删除job
-- 1.删除dbms job
BEGIN
SYS.DBMS_JOB.REMOVE(22);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
-- 2.删除scheduler job
begin
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'DBMS_JOB$_23');
end;
/
PL/SQL procedure successfully completed.
结论： 19c中，不管用上面哪个方式删除job，dbms_job 和 scheduler_job都会被同时删除.
数据迁移job问题
因为oracle数据库用户很多，当我们采用数据泵方式迁移时候，一般会统一用system用户进行导入/导出，可能会导致普通用户的job作业停止工作。
使用一下语句查看系统里所有的job:
SQL> select LOG_USER,PRIV_USER ,SCHEMA_USER,NEXT_DATE,broken from dba_jobs order by 1;
LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE B
---------- ---------- -------------------- ------------------- -
SYSTEM EEP EEP 2022-07-03 01:00:00 N
发现所有普通用户job的LOG_USER和PRIV_USER字段都变成了system,，而SCHEMA_USER还是原来的用户的schema名字。这是由于imp导入用户与job的属主用户不同造成的。
解决方法:
-
用job属主用户进行导入，不过重新导入麻烦。
-
更新dba_jobs视图
-- login sys as sysdba（以sysdba角色登录，执行一下语句修正两个字段LOG_USER和PRIV_USER的值为SCHEMA_USER字段的值）
SQL> update dba_jobs set log_user='username',priv_user='username' where schema_user='username';
SQL> commit;
注意： 在19c环境下，执行更新操作，会抛出如下错误，不在支持，在11g环境中可以执行。
SQL> update dba_jobs set priv_user='EEP' where schema_user='EEP';
update dba_jobs set priv_user='EEP' where schema_user='EEP'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
最后修改时间：2022-07-21 13:37:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
