您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 核心內建套件深度解析與 DBMS_JOB 進階排程實戰

編輯：Oracle 資料庫基礎

前言

Oracle 資料庫內建了數十個由 SYS 用戶擁有的 PL/SQL 套件，它們涵蓋了從進程間通訊、檔案 I/O 到動態 SQL 執行等核心功能。掌握這些套件是資料庫開發者與 DBA 進階的關鍵。本文將梳理最常用的核心套件，並重點實戰 DBMS_JOB 實現複雜的定時任務排程。

一、Oracle 核心內建套件速查表

套件名稱	核心功能
DBMS_ALERT	提供非輪詢機制，讓應用程式發送/接收事件警示，實現進程間非同步通訊。
DBMS_DDL	允許在 PL/SQL 區塊中動態執行 DDL 語句（如 `GRANT`、`REVOKE`、`ANALYZE`）。
DBMS_DESCRIBE	描述預存程序、函數的參數結構（參數名、型別、模式等），支援動態開發工具。
DBMS_JOB	傳統作業排程器，管理後台作業的提交、移除、修改執行時間與間隔（12c 後建議改用 `DBMS_SCHEDULER`）。
DBMS_LOB	操作大型物件（BLOB、CLOB、NCLOB、BFILE）的讀寫、複製、比較、載入等核心 API。
DBMS_OUTPUT	讓 PL/SQL 程式向客戶端緩衝區輸出訊息，是除錯與日誌記錄的基礎工具。
DBMS_PIPE	建立命名管道，實現資料庫 Session 之間的同步/非同步資料傳遞。
DBMS_SQL	動態 SQL 的核心引擎，支援運行時組建、解析、綁定、執行任意 SQL 語句。
DBMS_UTILITY	提供編譯 Schema、分析物件、獲取 CPU 時間、名稱解析、錯誤堆疊格式化等實用子程式。
UTL_FILE	讓 PL/SQL 直接讀寫作業系統層級的文字檔案（需建立 Directory 物件授權）。

二、DBMS_JOB 進階排程：突破簡單間隔的限制

DBMS_JOB.SUBMIT 的 INTERVAL 參數接受一個回傳 DATE 的字串運算式，Oracle 會在每次作業結束後評估該運算式以決定下次執行時間。利用這一特性，我們可以構建極其靈活的排程邏輯。

場景 1：每日固定時段、固定頻率執行（例：06:00-18:00 每小時）

VARIABLE jobno NUMBER;
VARIABLE instno NUMBER;
BEGIN
  SELECT instance_number INTO :instno FROM v$instance;
  
  -- 主作業：從今日 06:00 起，每小時執行一次（trunc 到小時）
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'statspack.snap;',
    next_date => TRUNC(SYSDATE) + 6/24,
    interval  => 'TRUNC(SYSDATE + 1/24, ''HH'')',
    no_parse  => TRUE,
    instance  => :instno
  );
  
  -- 停止作業：每日 18:00 執行 BROKEN 將主作業標記為失效
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'DBMS_JOB.BROKEN(' || :jobno || ', TRUE);',
    next_date => TRUNC(SYSDATE) + 18/24,
    interval  => 'TRUNC(SYSDATE + 1) + 18/24',
    no_parse  => TRUE,
    instance  => :instno
  );
  
  -- 重啟作業：次日 06:00 將主作業標記為正常
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'DBMS_JOB.BROKEN(' || :jobno || ', FALSE);',
    next_date => TRUNC(SYSDATE + 1) + 6/24,
    interval  => 'TRUNC(SYSDATE + 1) + 6/24',
    no_parse  => TRUE,
    instance  => :instno
  );
  COMMIT;
END;
/

場景 2：每 10 分鐘執行一次

DBMS_JOB.SUBMIT(
  job       => :jobno,
  what      => 'statspack.snap;',
  next_date => TRUNC(SYSDATE) + 1/144,  -- 10 分鐘 = 1/144 天
  interval  => 'TRUNC(SYSDATE + 1/144, ''MI'')',
  no_parse  => TRUE,
  instance  => :instno
);

場景 3：僅工作日（週一至週五）06:00 起每小時執行

DBMS_JOB.SUBMIT(
  job       => :jobno,
  what      => 'statspack.snap;',
  next_date => TRUNC(SYSDATE + 1) + 6/24,
  interval  => 'TRUNC(LEAST(
                  NEXT_DAY(SYSDATE, ''MONDAY''),
                  NEXT_DAY(SYSDATE, ''TUESDAY''),
                  NEXT_DAY(SYSDATE, ''WEDNESDAY''),
                  NEXT_DAY(SYSDATE, ''THURSDAY''),
                  NEXT_DAY(SYSDATE, ''FRIDAY'')
                ) + 1/24, ''HH'')',
  no_parse  => TRUE,
  instance  => :instno
);
COMMIT;

核心技巧：INTERVAL 運算式必須回傳一個 大於當前時間 的 DATE 值。利用 TRUNC、NEXT_DAY、LEAST 等日期函數組合，即可實現「工作日」、「每 N 分鐘」、「每日時段」等複雜業務場景。

三、DBMS_JOB 完整生命週期管理實戰

1. 建立測試環境

CREATE TABLE job_log (log_time DATE);

2. 建立執行程序

CREATE OR REPLACE PROCEDURE write_log AS
BEGIN
  INSERT INTO job_log VALUES (SYSDATE);
  COMMIT;
END;
/

3. 提交作業（每分鐘執行一次）

VARIABLE j1 NUMBER;
BEGIN
  -- 5/24/60 = 5 分鐘；1/24/60 = 1 分鐘
  DBMS_JOB.SUBMIT(:j1, 'write_log;', SYSDATE, 'SYSDATE + 1/24/60');
  COMMIT;
END;
/

4. 手動觸發/查看/修改/刪除

-- 立即執行一次
BEGIN DBMS_JOB.RUN(:j1); END;
/

-- 查看執行記錄
SELECT TO_CHAR(log_time, 'YYYY/MM/DD HH24:MI:SS') AS "執行時間" FROM job_log;

-- 修改下次執行時間（10 分鐘後）
BEGIN DBMS_JOB.NEXT_DATE(:j1, SYSDATE + 10/(24*60)); END;
/

-- 刪除作業
BEGIN DBMS_JOB.REMOVE(:j1); END;
/