Oracle透過gateway連線sqlserver2008

環境
oracle 10.2.0.1 ip:xxx.xxx.6.26 windows
sqlserver 2008  ip:xxx.xxx.6.49 DB:oracle

1.gateway11g 安裝在xxx.xxx.6.26上面，和oracle安裝在同一臺機器上
1).點選setup
2).輸入安裝路徑
3).選擇"Oracle Database Geteway for Microsoft Sql Server 11.2.0.1.0"
4).輸入SQL Server相關資訊

結束之後，配置資訊在E:\oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora
HS_FDS_CONNECT_INFO=xxx.xxx.6.49:1433//oracle
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

HS_FDS_CONNECT_INFO的格式是:
HS_FDS_CONNECT_INFO=sqlserver ip:portal//sqlserverDB

2.配置gateway11g的listener
E:\oracle\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = E:\oracle\product\11.2.0\tg_1)
      (PROGRAM = dg4msql)
    )
  )


LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
  )

ADR_BASE_LISTENER = E:\oracle\product\11.2.0\tg_1

3.配置oracle的tnsnames.ora
MSSQL =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
     (CONNECT_DATA =(SID = dg4msql))
     (HS = OK)
  )

C:\Users\is_long_li>tnsping mssql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-12月-2015 13:22:07
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
已使用的引數檔案:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621)) (CONNECT_DATA =(SID
= dg4msql)) (HS = OK))
OK (40 毫秒)

如股票出現 TNS-12557:協議介面卡不可載入 
需要將環境變數中的E:\oracle\product\11.2.0\tg_1\BIN,刪除了就可以了。

4.建立db link
C:\Users\is_long_li>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 28 13:10:37 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create database link mssql connect to "sa" identified by "sa123" using 'mssql';

資料庫連結已建立。

5.測試
在sqlserver中create table,insert data

create table tt(id int,name varchar(10))
insert tt values(1,'a');
insert tt values(2,'b');
insert tt values(3,'c');
insert tt values(4,'d');
insert tt values(5,'e');

在oracle查詢相關資料
SQL> select * from tt@mssql;

        id name
---------- ------------------------
         1 a
         2 b
         3 c
         4 d
         5 e