Blave的彩繪世界

環境如下：

1. SQL SERVER與Oracle Transparent Gateway裝在同一台。OTG可至Oracle Download裡，進入Oracle 11g，進入See all，就能下載Oracle Database Gateways，如(連結失效請自行找找看)：

Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
win32_11gR2_gateways.zip (595,036,736 bytes)

2. Oracle 11gR2 一部

3. SQL Server與TG可在不同台機器上

以下是設定步驟，懶得剪圖片了，有問題請發問(字的顏色是有相對應的，必須一致)：

1. 至 Transparent Gateway(以下簡稱TG) D:\product\11.2.0\tg_1\dg4msql\admin 新增兩個檔案："init[sid].ora" (如initcenter.ora及inithitedu)，內容：

initcenter.ora:
HS_FDS_CONNECT_INFO=127.0.0.1//center #連線到本機的SQL Server的center資料庫
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

inithitedu.ora:
HS_FDS_CONNECT_INFO=127.0.0.1//hitedu
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

2. 修改TG的 D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora，插入：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=center)
(ORACLE_HOME=D:\product\11.2.0\tg_1)
(PROGRAM=dg4msql)
)
(SID_DESC=
(SID_NAME=hitedu)
(ORACLE_HOME=D:\product\11.2.0\tg_1)
(PROGRAM=dg4msql)
)
)

3. 重開TG上的listener：
lsnrctl stop
lsnrctl start

4. 修改Oracle的tnsnames.ora，插入：
CENTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [TG的IP])(PORT = 1521))
)
(CONNECT_DATA =
(SID = center)
)
(HS = OK)
)

HITEDU = #自行命名
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = [TG的IP])()(PORT = 1521))
)
(CONNECT_DATA =
(SID = hitedu)
)
(HS = OK)
)

5. 建立Oracle的DB link:
CREATE PUBLIC DATABASE LINK LINK_CENTER CONNECT TO "sql server上的帳號" IDENTIFIED BY "對應的密碼" USING 'CENTER';
CREATE PUBLIC DATABASE LINK LINK_HITEDU CONNECT TO "sql server上的帳號" IDENTIFIED BY "對應的密碼" USING 'HITEDU';

6. 從Oracle上存取SQL Server上的資料：
SELECT * FROM USERS@LINK_CENTER

(完成)