Oracle與SQL Server異構資料遷移完整實戰：透明網關、OPENROWSET、連結伺服器

核心原則

• 跨資料庫SQL採用SQL-92標準語法
• 注意資料型別對應：Oracle DATE ↔ SQL Server DATETIME
• 遠端異構資料庫禁止DDL操作

一、Oracle透明網關存取SQL Server (Oracle → SQL Server)

1. 安裝與核心程式

Oracle 9i安裝時勾選TRANSPARENT GATEWAY FOR MSSQL，產生$ORACLE_HOME/bin/tg4msql.exe與$ORACLE_HOME/tg4msql/admin/inittg4msql.ora。

2. 透明網關配置 (inittg4msql.ora)

HS_FDS_CONNECT_INFO="SERVER=sql_host;DATABASE=pubs"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

3. Listener配置 (listener.ora)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = tg4msql) (PROGRAM = tg4msql) (SID_NAME = tg4msql) (ORACLE_HOME = /u01/app/oracle))
  )

關鍵：PROGRAM = tg4msql啟動網關程式。

4. TNS配置 (tnsnames.ora)

TG4MSQL =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sql_host)(PORT = 1527)))
    (CONNECT_DATA = (SID = tg4msql))
    (HS = OK)
  )

Port 1527對應Listener中網關的Address。

5. 建立Database Link

-- SQL Server建立登入帳號 xzh/xzh，授權pubs資料庫
CREATE DATABASE LINK sql_link CONNECT TO xzh IDENTIFIED BY xzh USING 'TG4MSQL';

-- 查詢驗證
SELECT * FROM sales@sql_link;

二、SQL Server存取Oracle (SQL Server → Oracle) 四種方式

1. OPENDATASOURCE (臨時連線)

SELECT * FROM OPENDATASOURCE('MSDAORA', 'Data Source=ora_tns;User ID=POS;Password=POS')..POS.A0325;

-- 跨庫建表
SELECT * INTO PUBS.DBO.A0325 
FROM OPENDATASOURCE('MSDAORA', 'Data Source=ora_tns;User ID=POS;Password=POS')..POS.A0325;

四部分命名：伺服器..擁有者.表名，擁有者/表名必須大寫。

2. 連結伺服器 (永久連線)

-- 建立
EXEC SP_ADDLINKEDSERVER @server='ORCL', @srvproduct='Oracle', @provider='MSDAORA', @datasrc='ora_tns';
-- 設定登入映射
EXEC SP_ADDLINKEDSRVLOGIN 'ORCL', FALSE, 'sa', 'POS', 'POS';

-- 查詢 (需企業管理器啟用「允許動態參數」或使用OPENQUERY)
SELECT * FROM ORCL..POS.A0325;
SELECT * FROM OPENQUERY(ORCL, 'SELECT * FROM POS.A0325');

3. OPENROWSET (單陳述式)

SELECT a.* FROM OPENROWSET('MSDAORA', 'ora_tns';'POS';'POS', POS.A0325) AS a ORDER BY a.ID;

4. ODBC方式

SELECT A.* FROM OPENROWSET('MSDASQL', 'ORCL_ODBC';'POS';'POS', POS.A0325) AS A ORDER BY A.ID;

三、效能與選擇建議

推薦：Oracle端用透明網關；SQL Server端用OPENROWSET/OPENDATASOURCE (OLE DB) 簡單高效，避開連結伺服器複雜配置。