Oracle Database Link 建立與 DBMS_JOB 定時任務實戰指南

一、Database Link 建立全流程

1. 確認目標庫連通性

在 $ORACLE_HOME/network/admin/tnsnames.ora 配置服務名（或直接使用 Easy Connect）：

TARGET_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.27.21)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = TESTDB))
  )

測試：tnsping TARGET_DB、sqlplus user/pwd@TARGET_DB。

2. 建立 DB Link

CREATE DATABASE LINK dblink_target
  CONNECT TO target_user IDENTIFIED BY target_pwd
  USING 'TARGET_DB';  -- 或 '172.19.27.21:1521/TESTDB'

權限需求：CREATE DATABASE LINK (私有) 或 CREATE PUBLIC DATABASE LINK (公共)。

3. 驗證與查詢

SELECT * FROM dual@dblink_target;          -- 基礎連通
SELECT * FROM target_table@dblink_target;  -- 業務表查詢

二、DBMS_JOB 定時任務配置

場景：每 2 分鐘透過 DB Link 向目標庫中間表插入數據。

1. 建立存儲過程（含異常處理）

CREATE OR REPLACE PROCEDURE sync_to_target IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 確保職務失敗不影響主事務
BEGIN
  INSERT INTO target_table@dblink_target (col1, col2, sync_time)
  SELECT col1, col2, SYSDATE FROM local_source_table
  WHERE sync_flag = 'N';
  
  UPDATE local_source_table SET sync_flag = 'Y' WHERE sync_flag = 'N';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Sync failed: ' || SQLERRM);
    -- 可選：寫入錯誤日誌表、發送告警郵件
END;
/

2. 提交 Job

VARIABLE job_no NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => :job_no,
    what      => 'sync_to_target;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/24/60'  -- 每 2 分鐘
  );
  COMMIT;
END;
/
PRINT job_no

3. 間隔表達式速查

4. 管理命令

-- 查看
SELECT job, what, next_date, interval, failures, broken 
FROM dba_jobs WHERE what LIKE '%sync_to_target%';

-- 強制立即執行一次
EXEC DBMS_JOB.RUN(job_no);

-- 修改間隔
EXEC DBMS_JOB.INTERVAL(job_no, 'SYSDATE + 1/24');  -- 改為每小時

-- 暫停/恢復
EXEC DBMS_JOB.BROKEN(job_no, TRUE);   -- 暫停
EXEC DBMS_JOB.BROKEN(job_no, FALSE);  -- 恢復

-- 刪除
EXEC DBMS_JOB.REMOVE(job_no);

三、遷移建議：DBMS_SCHEDULER (10g+ 推薦)

DBMS_JOB 為舊版 API，功能受限（無日曆、無事件驅動、無資源管理）。新專案請使用 DBMS_SCHEDULER：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SYNC_TO_TARGET_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'sync_to_target',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',  -- 每 2 分鐘
    enabled         => TRUE,
    comments        => 'Sync local to target via DB Link every 2 min'
  );
END;
/
-- 查看執行歷史
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='SYNC_TO_TARGET_JOB';

四、生產環境檢查清單

• DB Link 密碼加密：使用 CREATE DATABASE LINK ... AUTHENTICATED BY ... 或 Wallet。
• 網路超時：SQLNET.OUTBOUND_CONNECT_TIMEOUT、TCP.CONNECT_TIMEOUT 防止 Job 掛起。
• 冪等性：存儲過程需處理重複執行（唯一鍵、MERGE、標記欄位）。
• 監控告警：FAILURES > 0 或 BROKEN = 'Y' 觸發告警。
• 日誌保留：定期清理 SYS.JOB$ 歷史或啟用 Scheduler 自動清理。

掌握 DB Link + 定時任務組合，即可輕鬆實現跨庫數據同步、異構遷移、報表數據推送等常見集成場景。