您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle Heterogeneous Services 異構查詢實戰：透過 ODBC 連接 SQL Server

編輯：Oracle 資料庫基礎

為什麼需要 Heterogeneous Services (HS)？

企業常同時運行 Oracle、SQL Server、MySQL 等多種資料庫。傳統方案痛點：

應用層多連接池：程式碼複雜、分散式交易難控制。
Materialized View/Snapshot 定時同步：**非即時**、佔用儲存、維護成本高。

Oracle HS 提供 **透明網關** 能力：在 Oracle 端建立 DB Link，直接用 SQL 查詢非 Oracle 資料，**即時、透明、支援分散式交易**。

架構選型：透明網關 vs 通用連接

本文採用 **Generic Connectivity + ODBC** 連接 SQL Server 2000 示範。

環境

Oracle 8.1.7 on Windows 2000 Server
SQL Server 2000 中文版

步驟 1：確認 HS 元件已安裝

SELECT * FROM SYS.HS_BASE_CAPS;  -- 有資料即安裝

若無，從 Oracle 安裝媒體安裝「Heterogeneous Services」。

步驟 2：建立系統 DSN (ODBC)

控制台 → 管理工具 → 資料來源 (ODBC) → **系統 DSN** → 新增 → **SQL Server** → 名稱：dnsora2sql → 目標 SQL Server 實例、預設資料庫、認證方式。

⚠️ **必須用系統 DSN**，User DSN 僅當前用戶可見，Listener 服務帳號看不到。

步驟 3：配置 `tnsnames.ora` (Client 端)

LNK2SQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = hs4sql))
    (HS = OK)                     -- 關鍵：啟用 Heterogeneous Services
  )

步驟 4：配置 `listener.ora` (Server 端)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hs4sql)                 -- 必須與 tnsnames SID 一致
      (ORACLE_HOME = C:\Oracle\Ora92)
      (PROGRAM = hsodbc)                  -- ODBC 用 hsodbc，OLE DB 用 hsole
    )
  )

**重啟 Listener**：lsnrctl stop && lsnrctl start

步驟 5：建立 HS 初始化檔

位置：%ORACLE_HOME%\hs\admin\iniths4sql.ora

HS_FDS_CONNECT_INFO = dnsora2sql   -- 對應步驟 2 的系統 DSN 名稱
HS_FDS_TRACE_LEVEL = 0             -- 0=關閉，DEBUG 時設 1 或 255

步驟 6：建立 Database Link 並測試

CREATE DATABASE LINK ora2sql
  CONNECT TO sql1 IDENTIFIED BY sql1
  USING 'LNK2SQL';

-- 測試
SELECT * FROM region@ora2sql;

進階：支援分散式交易 (Two-Phase Commit)

在 SQL Server 建立復原帳號，賦予相應權限。

iniths4sql.ora 新增：

HS_FDS_RECOVERY_ACCOUNT = recovery_user
HS_FDS_RECOVERY_PWD = recovery_pwd

Oracle 端 DISTRIBUTED_TRANSACTIONS 參數足夠大。

常見問題排查表

類型	代理程式	適用場景	成本
Transparent Gateway	Oracle 專用（如 TG4MSQL、TG4SYBS）	生產環境、高效能、需分散式交易	需額外授權購買
Generic Connectivity	ODBC / OLE DB 驅動（隨 Oracle 附贈）	開發測試、低併發、異構查詢	免費

現象	可能原因	對策
ORA-28500 / ORA-28511	Listener 未註冊 HS SID、或 PROGRAM 錯誤	檢查 listener.ora SID_NAME、PROGRAM=hsodbc、重啟 Listener
ORA-28545: 連接失敗	DSN 名稱錯誤、SQL Server 未啟動、網路不通、防火牆	ODBC 管理員測試 DSN 連通、telnet 1433、檢查 iniths4sql.ora HS_FDS_CONNECT_INFO
中文亂碼	NLS_LANG / 字元集不匹配	Oracle 端 NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK，SQL Server 校對集一致
查詢極慢	無法下推 WHERE 條件、大量資料拉回本地過濾	建立 SQL Server 端視圖/索引、用 DRIVING_SITE HINT、考慮改用 Transparent Gateway
DDL 不支援	HS 僅支援 DML/查詢	DDL 操作需直接在 SQL Server 執行

效能優化建議

在 SQL Server 建立**覆蓋索引**，讓 HS 能下推謂詞。
使用 /*+ DRIVING_SITE(remote_table) */ 強制在遠端執行 Join/聚合。
調大 HS_FDS_FETCH_ROWS（預設 100，建議 500-1000）減少網路交互。
避免 SELECT *，只拉必要欄位。
大量資料同步改用 **External Table + Data Pump** 或 **GoldenGate/CDC**。

結語

Heterogeneous Services 讓 Oracle 成為企業資料**聯合查詢中樞**，以「零代碼改動、即時可見、標準 SQL」解決異構整合核心痛點。Generic Connectivity (ODBC) 雖免費但效能與功能受限（無並行、下推有限），生產環境高併發、需分散式交易場景，**請投資 Transparent Gateway**。部署關鍵在：**系統 DSN、Listener PROGRAM=hsodbc、tnsnames HS=OK、iniths4sql.ora 對應 DSN**，四個環節任一錯誤即連不上。