oracle和sqlserver互訪(2)

2.透過"透明閘道器"方式聯接

程式碼:

// A. 安裝透明閘道器,在安裝時選擇自定義安裝,安裝TRANSPARENT GATEWAY FOR SQLSERVER 元件,安裝成功後會產生oracle_homeora90tg4msql目錄!

// B. 配置TNSNAMES.ORA,路徑:ORACLE_HOMENETWORKADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!TG4MSQL =           # tnsName

    (DESCRIPTION =

      (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))     # 閘道器IP

      )

      (CONNECT_DATA =

        (SID = tg4msql )  #SID,要和監聽器裡的SID一致!

      )

    (HS=OK) 

    )// C. 配置listener.ora,路徑:ORACLE_HOMENETWORKADMINLISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = tg4msql)

      (SID_NAME = tg4msql)

      (ORACLE_HOME = D:oracleora9i)

      (PROGRAM= tg4msql)

    )

  )// D. 重啟監聽器服務

// E. 編輯ORACLE_HOMETG4MSQLADMIN內init.ora，這裡是inittg4msql.ora,因為上面的SID=tg4msql

#HS_FDS_CONNECT_INFO="SERVER=ERPSQL;DATABASE=EK",好多人說用這行可以,我用這行的時候出現了不能開啟連結的錯誤,改下面一行就沒問題了!HS_FDS_CONNECT_INFO=ERPSQL.EK

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER// F. 建立DB LINK,以及測試SQL>  create database link msql2 connect to "sa" identified by "pass" using 'TG4MSQL';

Database link created



SQL> select ta001,ta002 from acpta@msql2 where rownum<5;

TA001 TA002----- -----------S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001--------

程式碼:


SQL> set timing on

SQL> select ta001,ta002 from acpta@ora2sql where rownum<10;

TA001 TA002----- -----------S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

S710  20020328001

S710  20020329001

S710  20020419001

S710  20020422001

S710  20020425001



9 rows selected



Executed in 0.047 seconds



SQL> select ta001,ta002 from acpta@msql2 where rownum<10;

TA001 TA002----- -----------S710  20020306001

S710  20020315001

S710  20020325001

S710  20020326001

S710  20020328001

S710  20020329001

S710  20020419001

S710  20020422001

S710  20020425001



9 rows selected



Executed in 52.281 seconds--------

[@more@]