如何访问另一个oracle数据库

用database link

1、配置$ORACLE_HOME/networks/admin/tnsnames.ora

添加

orclsvr2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

2、create public database link srv2_link connect to username identified by password using 'orclsvr2'

3、这样使用select count(*) from tab@srv2_link

假设原来的表是test1

要写到的表是test2test1的字段为

a

b

ctest2的字段为

d

e

f

其中a和d对应

b和e对应

c和f对应先把要写到的表清掉，执行如下语句：truncate

table

test2然后把test1表的数据导入到test2表中，执行如下语句：insert

into

test2select

*

from

test1

ORACLE访问SQL SERVER数据库有一篇《Oracle 异构服务实践》讲得很清楚 但里面没有讲如何设置访问多个SQL Server数据库 我就补充一下 假设我们要在ORACLE里同时能访问SQL Server里默认的pubs和Northwind两个数据库在安装了ORACLE i Standard Edition或者ORACLE i Enterprise Edition的windows机器上(IP: ) 产品要选了透明网关(Oracle Transparent Gateway)里要访问Microsoft SQL Server数据库 $ORACLE I_HOME\tg msql\admin下新写initpubs ora和initnorthwind ora配置文件initpubs ora内容如下:HS_FDS_CONNECT_INFO= SERVER=sqlserver_hostnameDATABASE=pubs HS_DB_NAME=pubsHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVERinitnorthwind ora内容如下:HS_FDS_CONNECT_INFO= SERVER=sqlserver_hostnameDATABASE=Northwind HS_DB_NAME=NorthwindHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER(蓝色字的部分可以根据具体要访问的SQL Server数据库的情况而修改)$ORACLE I_HOME\neork\admin 下listener ora内容如下: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=tg msql))(SID_DESC=(SID_NAME=northwind)(ORACLE_HOME=d:\Oracle\Ora )(PROGRAM=tg msql)))重启动这台做gateway的windows机器上(IP: )TNSListener服务 (凡是按此步骤新增可访问的SQL Server数据库时 TNSListener服务都要重启动) ORACLE I ORACLE I的服务器端配置tnsnames ora 添加下面的内容: 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))保存tnsnames ora后 在命令行下tnsping pubstnsping 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则要求 多少有些不方便 oracle i和oracle i都可以在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@pubsselect zip from stores ERROR 位于第 行:ORA : 无效列名select zip from storeszip 已选择 行 用SQL Navigator或Toad看从SQL Server转移到ORACLE里的表的建表语句为:CREATE TABLE stores( stor_id 　CHAR( ) NOT NULL stor_name VARCHAR ( ) stor_address VARCHAR ( ) city VARCHAR ( ) state CHAR( ) zip 　CHAR( ))PCTFREE PCTUSED INITRANS MAXTRANS TABLESPACE　usersSTORAGE　 (INITIAL NEXT PCTINCREASE MINEXTENTS　 MAXEXTENTS　 )/总结: WINDOWS下ORACLE i网关服务器在$ORACLE I_HOME\tg msql\admin目录下的initsqlserver_databaseid oraWINDOWS下ORACLE i网关服务器listener ora里面(SID_DESC=(SID_NAME=sqlserver_databaseid)(ORACLE_HOME=d:\Oracle\Ora )(PROGRAM=tg msql))UNIX或WINDOWS下ORACLE I ORACLE I服务器tnsnames ora里面northwind =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )))(CONNECT_DATA =(SID = sqlserver_databaseid))(HS = sqlserver_databaseid))sqlserver_databaseid一致才行 lishixinzhi/Article/program/Oracle/201311/16587

---