Oracle跨库查询复制表的前置步骤
两种方式:
第一步、配置A服务器端的tnsnames.ora文件(TNSNAMES.ORA Network Configuration File),该文件存放的位置为:
$ORACLE_HOME/network/admin/tnsnames.ora
需要在该文件中增加对B库的配置项,格式如下
ZBCDB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.50.6)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = zbcdb3)
)
)
若在A库的另外一台客户端来访问B的数据库的话,同时也应该修改客户端的相应的文件。
第二步、在A服务器的一个库中建立B的一个数据的DBLINK。语法如下:
create database link dcmdb connect to dcmdb identified by dcmoptr using 'zbcdb3' ;
然后可以实现分布式查询:
select * from tabname@dcmdb where 1=1;
方式二:推荐,自己也使用的这种方式,不用麻烦去打开配置文件,用代码生成就行...
CREATE PUBLIC DATABASE LINK 数据链名称 CONNECT TO 登陆用户名 IDENTIFIED BY 密码 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 对方Oracle服务器的IP地址)(PORT = 端口号))
)
(CONNECT_DATA =
(SERVICE_NAME = 对方Oracle服务器服务名)
)
)'
但运行后发现:
此时需要权限。
先授权给用户root.
执行创建Database LINK,不再报错
执行跨库查询,已经返回远程库的结果,哈哈
贴上自己在操作过程中用到的代码:
---删除dblink
drop PUBLIC DATABASE LINK policy_dev;
--创建dblink
CREATE PUBLIC DATABASE LINK policy_dev CONNECT TO POLICY_DEV IDENTIFIED BY POLICY_DEV USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.133.221.49)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dwrep)
)
)'
--远程查询测试
select * from mid_procitemkind@policy_dev;
---删除dblink
drop PUBLIC DATABASE LINK prip_bzb;
--创建dblink
CREATE PUBLIC DATABASE LINK prip_bzb CONNECT TO prip_bzb IDENTIFIED BY prip_bzb USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.133.221.49)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dwrep)
)
)'
--查询测试
select * from batchnoinfo@prip_bzb;
--查询当前用户能看见的已经配置过的dblink
select * from ALL_DB_LINKS;