Oracle Heterogeneous Services 實戰：透過 ODBC 實現 Oracle 與 SQL Server 實時互訪

前言：異構數據整合的三種路徑

企業常面臨 Oracle、SQL Server、MySQL 共存需整合。常見方案：

1. 應用層多連接：代碼耦合度高、存儲過程需多版本維護。
2. 物化視圖/快照定時同步：非實時、存儲冗余、衝突解決複雜。
3. Oracle Heterogeneous Services (HS)：資料庫層透明訪問，像查本地表一樣查遠端異構表，支援實時查詢、分布式事務（透明網關）、DDL 操作。

本文以 **Oracle 8i/9i/10g/11g/12c/19c 通用**的 **Generic Connectivity (ODBC/OLE DB)** 為例，實現 Oracle → SQL Server 實時查詢。

架構原理

• Oracle 實例啟動 **HS Agent (hsodbc/hsole)** 進程。
• HS Agent 通過 **ODBC Driver Manager** 載入 **SQL Server ODBC Driver**。
• Oracle 發送 SQL → HS Agent 轉譯為 T-SQL → SQL Server 執行 → 結果集轉換回 Oracle 格式 → 返回客戶端。
• 對用戶透明：`SELECT * FROM table@dblink` 即可跨庫查詢。

環境準備

• Oracle Server：Windows 2000/2003/2008/2012/2016/2019（Linux 同理，ODBC 驅動換 unixODBC + FreeTDS/MS ODBC Driver for Linux）。
• 目標：SQL Server 2000/2005/2008/2012/2014/2016/2019。
• 網路互通，防火牆開放 1433 (SQL Server) + 1521 (Oracle Listener)。

配置步驟（Windows 平台）

1. 確認 HS 組件已安裝

SQL> SELECT * FROM sys.hs_base_caps;  -- 有行即安裝

若無，Universal Installer → Oracle Home → Custom → Heterogeneous Services。

2. 配置系統 DSN (64 位 Oracle 用 64 位 ODBC 管理員)

控制面板 → 管理工具 → **ODBC Data Sources (64-bit)** → 系統 DSN → 新增 → **ODBC Driver 17 for SQL Server** (或 SQL Server Native Client) → 名稱：`DSN_ORA2SQL` → 服務器 IP、認證方式、默認資料庫。

3. 配置 tnsnames.ora (`$ORACLE_HOME/network/admin`)

HS_SQLSERVER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = HS_SQLSERVER)
    )
    (HS = OK)
  )

• `SID` 需與 listener.ora 中 `SID_NAME` 一致。
• `HS = OK` 啟用異構服務。

4. 配置 listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = HS_SQLSERVER)
      (ORACLE_HOME = D:\app\oracle\product\19.0.0\dbhome_1)
      (PROGRAM = hsodbc)   -- OLE DB 用 hsole
      (ENVS = "LD_LIBRARY_PATH=/path/to/odbc/lib")  -- Linux 必須
    )
  )

5. 重載監聽器

lsnrctl reload

6. 配置 HS 初始化參數檔 (`$ORACLE_HOME/hs/admin/initHS_SQLSERVER.ora`)

HS_FDS_CONNECT_INFO = DSN_ORA2SQL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /path/to/libodbc.so   -- Linux 指定 ODBC 驅動路徑
# 可選調優參數
HS_FDS_FETCH_ROWS = 100
HS_FDS_RESULTSET_SUPPORT = TRUE
HS_NLS_NCHAR = UCS2
HS_LANGUAGE = AMERICAN_AMERICA.ZHS16GBK  -- 編碼匹配

7. 建立 Database Link

CREATE DATABASE LINK ora2sql
  CONNECT TO "sql_user" IDENTIFIED BY "sql_pass"
  USING 'HS_SQLSERVER';

• 雙引號保持大小寫敏感（SQL Server 登入名區分大小寫）。
• Link 名稱全域唯一。

8. 測試驗證

-- 簡單查詢
SELECT * FROM sys.tables@ora2sql;

-- 業務表查詢
SELECT * FROM sales.orders@ora2sql WHERE order_date > SYSDATE - 30;

-- 跨庫 Join（Oracle 驅動）
SELECT o.order_id, c.cust_name
  FROM orders@ora2sql o
  JOIN customers c ON o.cust_id = c.cust_id;

-- 執行遠端預存程序 (需 Transparent Gateway 而非 Generic Connectivity)
-- BEGIN proc@ora2sql; END;

常見錯誤與排查

效能優化清單

• 遠端表建立適當索引（HS 會將 WHERE 條件下推）。
• 調大 `HS_FDS_FETCH_ROWS` (默認 100 → 500-1000) 減少網路往返。
• 大量據拉取用 `CREATE TABLE AS SELECT ...@dblink` 或 `INSERT /*+ APPEND */ INTO ... SELECT ...@dblink`。
• 避免在 PL/SQL 循環中單行遠端查詢，改用集合綁定或臨時表批次處理。
• 啟用 `HS_FDS_RESULTSET_SUPPORT = TRUE` 支援預存程序 REF CURSOR 返回。

Generic Connectivity vs Transparent Gateway

現代化替代：Oracle Database Gateway for ODBC (DG4ODBC)

11gR2 起，`DG4ODBC` 取代舊 `hsodbc`，配置簡化：

• Listener 直接指定 `PROGRAM = dg4odbc`。
• 無需單獨 `init.ora`，參數可寫在 `listener.ora` 的 `ENVS` 或 `init.ora`。
• 支援 64 位原生、更穩定的驅動管理。

總結

Oracle Heterogeneous Services + Generic Connectivity (ODBC) 是零授權成本、配置標準化、實時跨庫查詢的經典方案。雖不支援分布式事務、效能不如原生 Gateway，但滿足 90%「Oracle 查 SQL Server」報表/同步/ETL 需求。生產部署務必：位數匹配、編碼統一、Fetch Size 調優、遠端索引到位、監控 Listener/HS Agent 日誌。

1. 上一篇文章： Delphi 應用中資料庫連接參數外部化最佳實踐：INI 檔 + 封裝單元完整方案
2. 下一篇文章： SQL 查詢結果添加自動編號的兩種實用技巧

Oracle 資料庫基礎

從 Oracle 到 SQL Server：基於語法樹的 SQL 智能翻譯器架構設計

動機：異構資料庫遷移的 SQL 轉換挑戰 簡單關鍵字替換 (

Oracle 快速找出欄位重複值：GROUP BY + COUNT 實戰

資料品質檢核時，最常見的需求就是「這個欄位有沒有重複值？」O

深入掌握 Oracle 層次查詢：從語法到實戰應用

在關聯式資料庫中，處理樹狀或階層式資料結構時，Oracle

Oracle 存儲結構深度解析：從物理文件到邏輯表空間

引子：表空間怎麼就滿了？ 某日師傅接到告警：某表空間滿了。原