Oracle9i透明网关操作Sql Server 2005

 安装透明网关
将 'Oracle Transparent Geteways 9.2.0.1.0'下的'Oracle Transparent Geteways for Microsoft SQL Server 9.2.0.1.0'选中；
将 'Oracle Transparent Geteways 9.2.0.1.0'下的'Oracle Transparent Geteways for Sybase 9.2.0.1.0'选中；

 

1 设置E:/oracle/ora92/tg4msql/admin 下inittg4msql.ora

HS_FDS_CONNECT_INFO="SERVER=.;DATABASE=PCDB2"
HS_DB_NAME=tg4msql
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

2 修改监听
E:/oracle/ora92/network/admin下
Listener.ora修改如下：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:/oracle/ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = LIST)
      (ORACLE_HOME = E:/oracle/ora92)
      (SID_NAME = LIST)
    )
#--------------------------------------------
  #此段为修改内容  
  (SID_DESC =
      (PROGRAM = tg4msql)
      (ORACLE_HOME = E:/oracle/ora92)      #oracle的主目录
      (SID_NAME = tg4msql)
     )
#----------------------------------------------
  )

E:/oracle/ora92/network/admin下
tnsnames.ora添加下列节点

tg4msql=  
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = LiShuitao)(PORT = 1521))  
      )
      (CONNECT_DATA =
        (SID = tg4msql)
      )
      (HS=tg4msql)
    )

3 配置init.ora

在C:/oracle/admin/实例名/pfile
将global_names设置为真值，及global_names=true
 

4 重启服务于监听

5 建立DB link

 CREATE  DATABASE LINK tg4mssql CONNECT TO "sa" IDENTIFIED BY "1" USING 'tg4msql';
--此处sa 和1 为sql server用户名和密码
--删除DB Link用
--drop database link tg4mssql;

6 查询sql server数据

select * from sysobjects@tg4mssql where rownum <=5;

select * from t_test@tg4mssql;

update t_test@tg4mssql set descs = 'aaa' where id = 1;

insert into t_test@tg4mssql(descs) values ('33333333');