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




