Oracle使用dblink连接SqlServer

Oracle使⽤dblink连接SqlServer

使⽤场景：当你需要从ORACLE数据库上访问另⼀台SqlServer数据库的数据时,Oracle提供了⼀个⼯具：gateways。通过这个⼯具，你可以创建dblink来连接sqlserver或其他不同公司的数据库----取决于你安装时的选项。

安装GATEWAYS后，使⽤下列2种⽅式可创建DBLINK⽅式A：

create database link bslink

connect to \"username\" identified by \"password\" using '( DESCRIPTION =

( ADDRESS = ( PROTOCOL = TCP )( HOST = remoteIP )( PORT = 1433 )) ( CONNECT_DATA = (

SID = SQLSERVER数据库名 ) ) ( HS=OK ) ) '

这种⽅式是不需要配置initdg4msql.ora和tnsnames.⽅式B：

1. 在$ORACLE_HOME/dg4msql/admin/initdg4msql ----这步可省略 使⽤⽅式A替代 配置initdg4msql.ora，以我的例⼦，如下： ******************************

复制代码

This is a customized agent init file that contains the HSparameters

that are needed for the Database Gateway for Microsoft SQLServer

HS init parameters

HS_FDS_CONNECT_INFO=[192.168.101.4]//bsdata ----只需要修改这个IP//数据库名HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER复制代码

2. 配置Oracle主⽬录下network\\admin⽬录下的listener.ora。以我的例⼦，如下：

LISTENER.ORA Network Configuration File:C:\\oracle\\ora92\\network\\admin\\listener.oraGenerated by Oracle configuration tools.

LISTENER = ----此处LISTENER名为LISTENER(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =

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

SID_LIST_LISTENER = -----此处SID_LIST_LINTENER名 同上为LISTENER(SID_LIST =

(SID_DESC =

(SID_NAME = dg4msql)

(ORACLE_HOME = D:\\app\\oracle\\product\\11.2.0\\dbhome_1)

(PROGRAM = dg4msql) ----此处PROGRAM对应TNSNAMES中的dg4msql))

3.配置Oracle主⽬录下network\\admin⽬录下的tnsnames.ora，以我的例⼦，如下：dg4msql = ---同上对应(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=dg4msql))(HS=OK))