前段时间跟一朋友聊SQL性能调优时突然说道DBLink这个话题,她就问我什么是DBLink,我当时解析的是:DBLink是可以实现跨数据库访问,是数据库之间的一种桥梁。
今天回想起来,就想做个总结:
- DBLink的定义?
DBLink是数据库的链接,就像电话线一样,是一个通道,当我们要跨本地数据库, 访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的DBLink, 通过DBLink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
- 基本语法
CREATE [SHARED] [PUBLIC] database link link_name
[CONNECT TO [user] [current_user] IDENTIFIED BY [password]
[AUTHENTICATED BY user IDENTIFIED BY password]
[USING ‘connect_string’]
方括号选项是可选的,其中public表示链接对数据库所有用户可用,而private则表示对创建用户可用,current_user一般用在多个分布式数据库中,connect_string表示数据库网络服务名,即连接字符串,tnsnames.ora中定义远程数据库的连接串,也可以在创建DBLink的时候直接指定。
- 创建dblink
**1,**在创建DBLink之前用户必须有创建dblink的权限,想知道有关dblink的权限,以sys用户登录到本地数据库:
select * from user_sys_privs t where t.privilege like upper('%link%');
查询结果集 :
SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
SYS CREATE PUBLIC DATABASE LINK NO
可以看出在数据库中dblink有三种权限:
1.CREATE DATABASE LINK(所创建的dblink只能是创建者能使用,别的用户使用不了) ,
2.CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用),
3.DROP PUBLIC DATABASE LINK。
**2,**在sys用户下,把CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASELINK权限授予给你的用户:
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to 该User;
**3,**创建两种DBLink的方式:
**3.1、**已经配置本地服务(tnsnames.ora中配置)
create public database link link_name
connect to username identified by password
using 'connect_string';
注:link_name是连接名字,可以自定义;
username是登陆数据库的用户名;
password是登陆数据库的用户密码;
connect_string是数据库连接字符串。
数据库连接字符串是当前客户端数据库中TNSNAMES.ORA文件里定义的别名名称.可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.
**3.2、**直接建立链接
create database link 要建立的dblink名称
connect to 远程数据库账号 identified by 远程数据库密码
using '(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=远程数据库地址)
(PORT=端口号)
)
(CONNECT_DATA=
(SERVICE_NAME=远程数据库名)
)
)';
- DBLink查询
select owner,object_name from dba_objects where object_type='DATABASE LINK';
或者
select * from dba_db_links;
- dblink删除
DROP PUBLIC DATABASE LINK link_name;
- dblink使用
SELECT……FROM 表名@数据库链接名;
查询、删除和插入数据和操作本地的数据库是一样的,只不过表名需要写成“表名@dblink服务器”而已。
例:查询北京数据库中emp表数据 select * from emp@BeiJing;
设此处北京数据库的数据库连接字符串为BeiJing;
- 同义词配合
例子中from emp@BeiJing可以创建同义词来替代:
CREATE SYNONYM 同义词名 FOR 表名;
CREATE SYNONYM 同义词名 FOR 表名@数据库链接名;
如:create synonym bj_scott_emp for emp@BeiJing;
于是就可以用bj_scott_emp来替代带@符号的分布式链接操作emp@BeiJing,
DB LINK是独立于创建用户(USER_DB_LINKS的USERNAME)起作用的,其他用户无法使用这个连接,无权限也不能删除它。