Oracle 10g R2 變更通知機制：ODP.NET 實現資料庫主動推送至應用層快取

痛點：傳統快取同步困境

• 手動刷新：依賴用戶記憶，易造成髒讀
• 定時輪詢：無法設定完美間隔，要不延遲、要不浪費資源

Database Change Notification (DCN) 架構

1. 註冊：指定監聽查詢，ODP.NET 自動註冊 DML/DDL/Global 事件
2. 通知：資料異動時資料庫主動發送通知
3. 響應：應用層收到通知 → 自動刷新快取或提示用戶

ODP.NET 實戰範例

using System.Threading; using System.Data; using Oracle.DataAccess.Client;

static bool notificationReceived = false;

static void Main() {
  string sql = "SELECT first_name, last_name, salary FROM employees WHERE employee_id = 149";
  string connStr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
  
  using (OracleConnection con = new OracleConnection(connStr))
  using (OracleCommand cmd = new OracleCommand(sql, con)) {
    con.Open();
    OracleDependency dep = new OracleDependency(cmd);
    dep.OnChange += OnDatabaseNotification;
    cmd.ExecuteNonQuery();
    
    while (!notificationReceived) {
      Console.WriteLine("Waiting for notification...");
      Thread.Sleep(2000);
    }
  }
  Console.WriteLine("Press ENTER to continue...");
  Console.ReadLine();
}

static void OnDatabaseNotification(object src, OracleNotificationEventArgs args) {
  Console.WriteLine("Database Change Notification received!");
  DataTable dt = args.Details;
  Console.WriteLine("Resource {0} has changed.", dt.Rows[0]["ResourceName"]);
  notificationReceived = true;
}

前置權限

GRANT CHANGE NOTIFICATION TO hr;

驗證步驟

1. 執行程式，顯示「Waiting...」
2. 另開 Session：UPDATE employees SET salary=salary+10 WHERE employee_id=149; COMMIT;
3. 程式輸出：Database Change Notification received! Resource HR.EMPLOYEES has changed.

適用場景

• 高頻讀取、低頻寫入的參考資料快取
• 儀表板即時數據刷新
• 多節點應用叢集快取一致性維護

注意事項

• 需 Oracle 10g R2+ 資料庫與 ODP.NET 10.2+
• 註冊查詢不支援所有 SQL 語法（如複雜 Join、聚合），請參考文件限制
• 大量註冊可能影響資料庫效能，建議監控 USER_CHANGE_NOTIFICATION_REGS