全部產品
Search
文件中心

PolarDB:DBMS_JOB

更新時間:Dec 20, 2024

PolarDB提供DBMS_JOB包調度與管理定時任務。本文為您介紹如何建立、管理和刪除一個定時任務。

前提條件

  • PolarDB叢集的核心版本為V1.1.7及以上版本。

    說明

    您可通過如下語句查看PolarDB PostgreSQL版(相容Oracle)的核心小版本號碼:

    SHOW polar_version;
  • 由於安全原因,DBMS_JOB外掛程式暫時不支援手動建立,如有需要請聯絡我們處理。

注意事項

  • DBMS_JOB僅支援高許可權使用者使用。如何建立高許可權帳號,請參見建立資料庫帳號

  • 目前僅支援在postgres庫中建立DBMS_JOB外掛程式,如果您需要在其他庫中使用定時任務,可以在postgres庫中配置跨庫任務。具體操作,請參見跨庫執行定時任務

準備測試資料

說明

測試資料僅適用本文中的操作樣本。

建立一張名為jobrun的表用於測試,樣本如下:

CREATE TABLE jobrun (
    id        serial NOT NULL PRIMARY KEY,
    runtime   VARCHAR2(40)
);

建立一個名為job_proc的預存程序,樣本如下:

CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;

刪除外掛程式

警告

刪除外掛程式時會刪除所有已有的使用者任務。

刪除外掛程式文法如下:

DROP EXTENSION dbms_job CASCADE;

建立定時任務

文法

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])

表 1. 參數說明

參數

說明

job

請傳入jobid,用於返回本次提交任務的ID。該ID自動產生且對每個任務都唯一。

what

調用預存程序名稱,不可為空白。本次樣本中使用了job_proc預存程序。

next_date

定時任務的開始時間,如果不傳入則預設為目前時間。

interval

定時任務的執行間隔。具體內容,請參見INTERVAL參考

表 2. INTERVAL參考

定時任務執行間隔

樣本

每分鐘執行

TRUNC(sysdate,'mi') + 1/(24*60)

每天定時執行

每天淩晨1點執行:

TRUNC(sysdate) + 1 + 1/(24)

每周定時執行

每周一淩晨1點執行:

TRUNC(next_day(sysdate,'monday')) + 1/24

每月定時執行

每月1日淩晨1點執行:

TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24

每季度定時執行

每季度的第一天淩晨1點執行:

TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

每年定時執行

每年1月1日淩晨1點執行:

ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24

固定時間點執行

每天早上的8點10分運行:

Trunc(Sysdate+1) + (8*60+10)/24*60

固定時間間隔執行

每個小時的第15分鐘運行,例如8點15分、9點15分、10點15分等。

Trunc(sysdate,'hh') + (60+15)/(24*60)

調用job_proc預存程序建立一個定時任務,樣本如下:

DECLARE
   jobid INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;
說明

如果定時任務中的時間使用了單引號,這種嵌套會導致語法錯誤,錯誤樣本如下:

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

您需要將對應的參數改為$$的形式來避免這種錯誤,正確樣本如下:

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

查看定時任務

您可以通過如下語句查看目前使用者建立的定時工作清單:

SELECT * FROM sys.user_jobs;

修改定時任務的內容、執行時間和執行間隔

文法

CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)

表 3. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

what

調用預存程序名稱。

說明

如果保持當前值不變,可將此參數設定為NULL

next_date

定時任務的開始時間。

說明

如果保持當前值不變,可將此參數設定為NULL

interval

定時任務的執行間隔。具體內容,請參見INTERVAL參考

說明

如果保持當前值不變,可將此參數設定為NULL

修改任務ID為1的定時任務,調用的預存程序不變,定時任務開始時間修改為2020年12月29日,任務執行間隔設定為每個小時的15分執行一次。樣本如下:

BEGIN
    DBMS_JOB.CHANGE(1,NULL,TO_DATE('29-DEC-20','DD-MON-YY'),$$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

修改定時任務的執行間隔

文法

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)

表 4. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

interval

定時任務的執行間隔。具體內容,請參見INTERVAL參考

修改任務ID為1的定時任務,任務執行間隔修改為每天淩晨1點執行一次。樣本如下:

BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

修改定時任務的執行時間

文法

NEXT_DATE(job BINARY_INTEGER, next_date DATE)

表 5. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

next_date

定時任務的開始時間。

修改任務ID為1的定時任務,定時任務開始時間設定為2020年12月30日。樣本如下:

BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

修改定時任務的內容

文法

WHAT(job BINARY_INTEGER, what VARCHAR2)

表 6. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

what

調用的預存程序名稱。

修改任務ID為1的定時任務,將調用的預存程序改為job_proc2。樣本如下:

BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

停止和啟動定時任務

文法

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])

表 7. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

broken

定時任務的狀態,取值如下:

  • true:定時任務的狀態設定為已損壞,設定為損壞的定時任務將停止運行,您可以通過dbms_job.run強行運行已損壞狀態的定時任務。

  • false:定時任務的狀態設定為正常運行。

next_date

定時任務的開始時間,如果不傳入則預設為目前時間。

將任務ID為1的定時任務設定為已損壞狀態。樣本如下:

BEGIN
    DBMS_JOB.BROKEN(1,true);
END;

將任務ID為1的定時任務設定為正常運行狀態。樣本如下:

BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

強制啟動定時任務

文法

RUN(job BINARY_INTEGER)

表 8. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

強制啟動任務ID為1的定時任務。樣本如下:

BEGIN
    DBMS_JOB.RUN(1);
END;
說明

如果在使用相關用戶端串連工具執行上述語句時遇到類似以下的錯誤提示,則表明當前用戶端環境尚未適配。可更換用戶端串連工具為PolarDB-Tools解決此類問題。

ERROR: syntax error at end of input
CONTEXT:polar-spl function dbms_job._run_job(integer,boolean) line 151 at RAISE line 547 of package body

刪除定時任務

文法

REMOVE(job BINARY_INTEGER)

表 9. 參數說明

參數

說明

job

任務ID,如何查看請參見查看定時任務

刪除任務ID為1的定時任務。樣本如下:

BEGIN
    DBMS_JOB.REMOVE(1);
END;

查看任務執行記錄

您可以通過如下語句查看定時任務的執行記錄:

SELECT * FROM dbmsjob.pga_joblog;

跨庫執行定時任務

由於DBMS_JOB只能配置在postgres庫,其他庫如果需要設定定時任務則需要進行跨庫任務。

以下樣本中,DBMS_JOB的配置庫為postgres,運行庫為test,需要在test庫中的某張表每分鐘插入一條資料。如何建立資料庫,請參見建立資料庫

  1. test庫中建立一個名為jobrun的表和一個名為job_proc的預存程序。

    • 建立名為jobrun的表,命令如下:

      CREATE TABLE public.jobrun (
          id       serial NOT NULL PRIMARY KEY,
          runtime  VARCHAR2(40)
      );
    • 建立名為job_proc的預存程序,命令如下:

      CREATE PROCEDURE public.job_proc
      IS
      BEGIN
          INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
      END;
  2. 切換到postgres庫,建立定時任務。

    您需要在DBMS_JOB.SUBMIT()函數中添加目標庫,本例中目標庫名為test。其他參數請參見建立定時任務

    樣本如下:

    DECLARE
       jobid  INTEGER;
    BEGIN
       DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
    END;
  3. postgres中查看定時任務的狀態和執行記錄。

    • 查看定時任務:

      SELECT * FROM sys.user_jobs;

      返回結果如下:

       job | jobloguser | job_user | database |           job_created            |           job_changed            |            last_date             | last_sec |         next_date         | next_sec |   total_time    | broken |                 interval                 | failures |                                                      what                                                       | instance
      -----+------------+----------+----------+----------------------------------+----------------------------------+----------------------------------+----------+---------------------------+----------+-----------------+--------+------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------+----------
         1 | DBUSER     | dbuser   | postgres | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:51:12.025001 +00:00 | 02:51:12 | 29-OCT-20 02:53:12 +00:00 | 02:53:12 | 00:00:00.243224 | N      | BEGIN return SYSDATE + 1/(24 * 30); END; |        0 |  BEGIN EXECUTE IMMEDIATE 'SELECT dbmsjob.dbms_job_internal_job_link(''BEGIN job_proc; END;'', ''test'');' ; END |        0
    • 查看執行記錄:

      SELECT * FROM dbmsjob.pga_joblog;

      返回結果如下:

       jlgid | jlgjobid | jlgstatus |             jlgstart             |   jlgduration   
      -------+----------+-----------+----------------------------------+-----------------
           1 |        1 | s         | 29-OCT-20 02:38:49.762995 +00:00 | 00:00:00.017495
           2 |        1 | s         | 29-OCT-20 02:39:50.061113 +00:00 | 00:00:00.016463
           3 |        1 | s         | 29-OCT-20 02:40:50.062331 +00:00 | 00:00:00.016244
  4. 切換到test庫,查看錶中資料。

    查詢命令如下:

    SELECT * FROM jobrun;

    查詢結果如下:

     id |               runtime               
    ----+-------------------------------------
      1 | job_proc run at 2020-10-29 02:38:50
      2 | job_proc run at 2020-10-29 02:39:50
      3 | job_proc run at 2020-10-29 02:40:50
說明

如果您需要修改跨庫任務的資訊,需要在postgres執行修改函數時附加對應的資料庫名稱。例如將上述樣本中每分鐘運行一次的計劃改為每兩分鐘運行一次,樣本如下:

BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;