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的屬主用戶不同造成的。

解決方法:

1. 用job屬主用戶進行導入，不過重新導入麻煩。

2. 更新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