环境
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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-1965861/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-1965861/