一、DBMS_JOB.SUBMIT 參數說明
| 參數 | 型別 | 說明 |
|---|
| JOB | OUT BINARY_INTEGER | 系統自動分配的任務編號 |
| WHAT | IN VARCHAR2 | 要執行的 PL/SQL 程式碼 (建議用 BEGIN proc; END;) |
| NEXT_DATE | IN DATE DEFAULT SYSDATE | 下次執行時間 |
| INTERVAL | IN VARCHAR2 DEFAULT 'NULL' | 日期運算式，計算下次執行時間 |
| NO_PARSE | IN BOOLEAN DEFAULT FALSE | TRUE=延遲解析至執行時 |
二、完整建立範例：每分鐘備份新增資料
-- 1. 來源表
CREATE TABLE person (id NUMBER PRIMARY KEY, username VARCHAR2(255), age NUMBER, password VARCHAR2(255));
-- 2. 備份表
CREATE TABLE person_back (id NUMBER PRIMARY KEY, username VARCHAR2(255), age NUMBER, password VARCHAR2(255));
-- 3. 記錄最大 ID 輔助表
CREATE TABLE tb_maxid (id NUMBER);
INSERT INTO tb_maxid VALUES (0);
-- 4. 備份預存程序 (分段提交避免 UNDO 爆滿)
CREATE OR REPLACE PROCEDURE pro_back_person(v_maxid NUMBER) IS
CURSOR c_person IS SELECT * FROM person WHERE id > v_maxid;
r_person person%ROWTYPE;
v_idx NUMBER := 0;
v_max NUMBER;
BEGIN
IF NOT c_person%ISOPEN THEN OPEN c_person; END IF;
LOOP
FETCH c_person INTO r_person; EXIT WHEN c_person%NOTFOUND;
INSERT INTO person_back VALUES (r_person.id, r_person.username, r_person.age, r_person.password);
v_idx := v_idx + 1;
IF v_idx = 2000 THEN COMMIT; v_idx := 0; END IF;
END LOOP;
COMMIT;
SELECT MAX(id) INTO v_max FROM person_back;
UPDATE tb_maxid SET id = v_max;
COMMIT;
CLOSE c_person;
END;
-- 5. 呼叫包裝程序
CREATE OR REPLACE PROCEDURE invokebackpro IS
v_maxid NUMBER;
BEGIN
SELECT id INTO v_maxid FROM tb_maxid;
pro_back_person(v_maxid);
END;
-- 6. 建立排程 (每分鐘)
DECLARE
v_jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => v_jobno,
what => 'invokebackpro;',
interval => 'TRUNC(SYSDATE,''MI'') + 1/(24*60)'
);
COMMIT;
END;
/
-- 查看
SELECT job, broken, what, interval, last_date, next_date FROM user_jobs;
三、常用 INTERVAL 寫法速查表
| 需求 | INTERVAL 運算式 |
|---|
| 每分鐘 | TRUNC(SYSDATE,'MI') + 1/(24*60) |
| 每小時 | TRUNC(SYSDATE,'HH') + 1/24 |
| 每天凌晨 2 點 | TRUNC(SYSDATE) + 1 + 2/24 |
| 每週一凌晨 2 點 | TRUNC(NEXT_DAY(SYSDATE,1)) + 2/24 |
| 每月 1 號凌晨 2 點 | TRUNC(ADD_MONTHS(SYSDATE,1)) + 2/24 |
| 每季首日 | TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 |
| 每半年 | TRUNC(ADD_MONTHS(SYSDATE,6),'YYYY') + 2/24 |
| 每年 1/1 | TRUNC(ADD_MONTHS(SYSDATE,12),'YYYY') + 2/24 |
| 僅執行一次 | NULL |
四、任務管理常用指令
-- 立即執行一次
BEGIN DBMS_JOB.RUN(1); COMMIT; END;
-- 修改執行內容
BEGIN DBMS_JOB.WHAT(1, 'new_proc;'); COMMIT; END;
-- 修改下次執行時間
BEGIN DBMS_JOB.NEXT_DATE(1, SYSDATE+1); COMMIT; END;
-- 修改間隔
BEGIN DBMS_JOB.INTERVAL(1, 'TRUNC(SYSDATE)+1'); COMMIT; END;
-- 標記失效 (停止執行)
BEGIN DBMS_JOB.BROKEN(1, TRUE); COMMIT; END;
-- 移除任務
BEGIN DBMS_JOB.REMOVE(1); COMMIT; END;
五、關鍵前提：JOB_QUEUE_PROCESSES > 0
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 SCOPE=BOTH;
-- 為 0 時 CJQ0 不啟動，任務永不執行
六、監控視圖
DBA_JOBS / USER_JOBS：任務定義與下次執行時間DBA_JOBS_RUNNING：正在執行的任務
