最近遇到在不同的数据库之间查询数据的问题,所以查看oracle sql reference后总结下子。
创建db link 有两种方式:
1.私有的(默认)--用户必须具有create database link 系统权限
create database link dbline01
connect to user_name identified by password -- user_name/password为远端的数据库用户名
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 库名)
)
)';
2.公有的 --用户必须具有create public database link 系统权限及远端数据库的create session 系统权限
create public database link dblink02
connect to user_name identified by password -- user_name/password为远端的数据库用户名
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 库名)
)
)';
连接成功后,测试
select * from SELECT * FROM t@dblink02; --正常显示数据 则成功!
删除 database link
drop database link dblink01;
drop public database link dblink02;
接下来总结下通过创建database link实现ORACLE跨数据库查询的方法 :
1.配置本地数据库服务器的tnsnames.ora文件
$vi $ORACLE_HOME/network/admin/tnsnames.ora
添加如下行,其中DBLINK为连接名(可自定义),HOST和PORT为数据库侦听的IP及端口,SERVICE_NAME为数据库的SID,
MEDIADBLINK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db)
)
)
2.登录到本地数据库,创建database link
执行如下查询语句,其中MEDIADB为database link名(可自定义),MEDIADBLINK为先前在tnsnames.ora中定义的连接名,
dbuser为用户名,password为密码
-- Create database link
create database link MEDIADB
connect to dbuser identified by password
using 'MEDIADBLINK';
注意:这里不会验证用户名密码的正确性
3.使用链接的数据库
3.1 查询、删除和插入数据和操作本地的数据库是一样的,只不过表名需要写成“表名@database link名”,如
select * from table_name@MEDIADB ;
3.2 也可以为这个表创建一个同义词
create synonym aaa for table_name@MEDIADB ;
如下语句的效果和3.1中的一样
select * from aaa;
删除同义词的语句为
drop synonym aaa;