ORACLE访问SQL SERVER数据库有一篇《Oracle 异构服务实践》讲得很清楚
但里面没有讲如何设置访问多个SQL Server数据库 我就补充一下
假设我们要在ORACLE里同时能访问SQL Server里默认的pubs和Northwind两个数据库
在安装了ORACLEi Standard Edition或者ORACLEi Enterprise Edition的windows机器上(IP:)
产品要选了透明网关(Oracle Transparent Gateway)里要访问Microsoft SQL Server数据库
$ORACLEI_HOME\tgmsql\admin下新写initpubsora和initnorthwindora配置文件
initpubsora内容如下:
HS_FDS_CONNECT_INFO=SERVER=sqlserver_hostname;DATABASE=pubs
HS_DB_NAME=pubs
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
initnorthwindora内容如下:
HS_FDS_CONNECT_INFO=SERVER=sqlserver_hostname;DATABASE=Northwind
HS_DB_NAME=Northwind
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
(蓝色字的部分可以根据具体要访问的SQL Server数据库的情况而修改)
$ORACLEI_HOME\network\admin 下listenerora内容如下:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = d:\oracle\ora)
(SID_NAME = test)
)
(SID_DESC=
(SID_NAME=pubs)
(ORACLE_HOME=d:\Oracle\Ora)
(PROGRAM=tgmsql)
)
(SID_DESC=
(SID_NAME=northwind)
(ORACLE_HOME=d:\Oracle\Ora)
(PROGRAM=tgmsql)
)
)
重启动这台做gateway的windows机器上(IP:)TNSListener服务
(凡是按此步骤新增可访问的SQL Server数据库时TNSListener服务都要重启动)
ORACLEIORACLEI的服务器端配置tnsnamesora 添加下面的内容:
pubs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SID = pubs)
)
(HS = pubs)
)
northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SID = northwind)
)
(HS = northwind)
)
保存tnsnamesora后在命令行下
tnsping pubs
tnsping northwind
出现类似提示即为成功
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )
(PORT = ))) (CONNECT_DATA = (SID = pubs)) (HS = pubs))
OK（毫秒）
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )
(PORT = ))) (CONNECT_DATA = (SID = northwind)) (HS = northwind))
OK（毫秒）
设置数据库参数global_names=false
设置global_names=false不要求建立的数据库链接和目的数据库的全局名称一致
global_names=true则要求 多少有些不方便
oraclei和oraclei都可以在DBA用户下用SQL命令改变global_names参数
alter system set global_names=false;
建立公有的数据库链接:
create public database link pubs connect to testuser identified by testuser_pwd using pubs;
create public database link northwind connect to testuser identified by testuser_pwd using northwind;
(假设SQL Server下pubs和northwind已有足够权限的用户登录testuser密码为testuser_pwd)
访问SQL Server下数据库里的数据:
select * from stores@pubs;
select * from region@northwind;
使用时的注意事项
ORACLE通过访问SQL Server的数据库链接时用select * 的时候字段名是用双引号引起来的
例如
create table stores as select * from stores@pubs;
select zip from stores
ERROR 位于第 行:
ORA: 无效列名
select zip from stores;
zip
已选择行
用SQL Navigator或Toad看从SQL Server转移到ORACLE里的表的建表语句为:
CREATE TABLE stores
(stor_idCHAR() NOT NULL
stor_nameVARCHAR()
stor_address VARCHAR()
city VARCHAR()
stateCHAR()
zipCHAR())
PCTFREE
PCTUSED
INITRANS
MAXTRANS
TABLESPACEusers
STORAGE (
INITIAL
NEXT
PCTINCREASE
MINEXTENTS
MAXEXTENTS
)
/
总结: WINDOWS下ORACLEi网关服务器在$ORACLEI_HOME\tgmsql\admin目录下的initsqlserver_databaseidora
WINDOWS下ORACLEi网关服务器listenerora里面
(SID_DESC=
(SID_NAME=sqlserver_databaseid)
(ORACLE_HOME=d:\Oracle\Ora)
(PROGRAM=tgmsql)
)
UNIX或WINDOWS下ORACLEIORACLEI服务器tnsnamesora里面
northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SID = sqlserver_databaseid)
)
(HS = sqlserver_databaseid)
)
sqlserver_databaseid一致才行
