DBMS_SCHEDULER 定時任務實戰：Shell 腳本備份與 PL/SQL 批量入庫

Oracle 內建排程器 DBMS_SCHEDULER 完整使用指南

DBMS_SCHEDULER 取代舊版 DBMS_JOB，支援 PL/SQL 塊、存儲過程、外部可執行文件、鏈式任務，日誌查詢方便、隨庫遷移、故障可重試。本文以 RMAN 備份 Shell 腳本與 PL/SQL 批量插入為例，涵蓋創建、啟用、手動執行、查詢日誌、刪除全生命週期。

1. 準備外部備份腳本 (db_backup.sh)

#!/bin/sh
ORACLE_SID=sydb; export ORACLE_SID
ORACLE_HOME=/u01/app/product/11.2.0/db_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
LOCAL=/EXPORT; export LOCAL

$ORACLE_HOME/bin/rman target sys/oracle catalog rman/rman \
  log='/tmp/db_backup.log' append <
關鍵點：腳本內必須完整導出環境變量，否則排程器環境極簡會導致 rman: command not found 或連接失敗。

2. 創建排程任務 (每月 1 號、15 號、末日 03:00 執行)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'DB_BACKUP',
    job_type        => 'EXECUTABLE',
    job_action      => '/oracle/db_backup.sh',
    start_date      => TO_DATE('2015-05-19 03:00:00','YYYY-MM-DD HH24:MI:SS'),
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1,15,-1',  -- -1 代表月末
    enabled         => TRUE,
    comments        => 'Monthly incremental backup with archivelogs');
END;
/


3. job_type 取值詳解

類型
說明
參數限制
PLSQL_BLOCK
匿名 PL/SQL 塊
不支援參數，number_of_arguments=0
STORED_PROCEDURE
存儲過程 (PL/SQL/Java/C)
僅過程，不支援帶返回值的函數
EXECUTABLE
OS 命令/腳本 (.sh/.bat/.exe)
需 CREATE EXTERNAL JOB 權限，不支援 ANYDATA 參數
CHAIN
任務鏈 (多步驟依賴)
不支援參數


4. PLSQL_BLOCK 示例：立即執行批量插入
CREATE TABLE t_insert (cname VARCHAR2(30), cnum NUMBER);
CREATE OR REPLACE PROCEDURE fun_insertdata AS
  v_sql VARCHAR2(300);
BEGIN
  v_sql := 'INSERT INTO t_insert(cname,cnum) VALUES (:1,:2)';
  FOR i IN 1..10000 LOOP
    EXECUTE IMMEDIATE v_sql USING 'AAA', i;
  END LOOP;
  COMMIT;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'INSERT_DATATOTAB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN fun_insertdata; END;',
    start_date      => NULL,           -- 立即執行
    repeat_interval => NULL,           -- 不重複
    enabled         => TRUE,
    comments        => 'One-time bulk insert');
END;
/


5. 常用管理操作
-- 手動執行
EXEC DBMS_SCHEDULER.RUN_JOB('DB_BACKUP');
-- 啟用/禁用
EXEC DBMS_SCHEDULER.ENABLE('DB_BACKUP');
EXEC DBMS_SCHEDULER.DISABLE('DB_BACKUP');
-- 查看執行歷史
SELECT job_name, status, actual_start_date, run_duration, error#
FROM   dba_scheduler_job_run_details
WHERE  job_name = 'DB_BACKUP'
ORDER BY log_date DESC;
-- 刪除任務
EXEC DBMS_SCHEDULER.DROP_JOB('DB_BACKUP');

6. repeat_interval  calendrier 表達式速查

頻率
關鍵字
示例
每年
YEARLY
FREQ=YEARLY;BYMONTH=1;BYMONTHDAY=1
每月
MONTHLY
FREQ=MONTHLY;BYMONTHDAY=1,15,-1
每周
WEEKLY
FREQ=WEEKLY;BYDAY=MON,WED,FRI
每天
DAILY
FREQ=DAILY;BYHOUR=2;BYMINUTE=30
每小時
HOURLY
FREQ=HOURLY;BYMINUTE=0
每分鐘
MINUTELY
FREQ=MINUTELY


7. 權限要求

創建任務：CREATE JOB 系統權限。
外部任務：額外需 CREATE EXTERNAL JOB。
查看所有任務：SELECT * FROM dba_scheduler_jobs;