【DB笔试面试520】在Oracle中，Oracle的DBLINK是什么？
【DB笔试面试520】在Oracle中，Oracle的DBLINK是什么？
♣
题目部分
在Oracle中，Oracle的DBLINK是什么？
♣
答案部分
当用户要跨本地数据库访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的DBLINK，通过DBLINK可以像访问本地数据库一样访问远程数据库表中的数据。其实，DBLINK和数据库中的VIEW差不多，创建DBLINK的时候需要知道待读取数据库的IP地址、ORACLE_SID以及数据库用户名和密码。
在创建DBLINK之前，普通用户必须具有相关的权限才能创建DBLINK，以SYS用户登录到本地数据库可以看到相关的权限：
SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');
可以看出，在数据库中DBLINK有3种权限：CREATE DATABASE LINK（所创建的DBLINK只能是创建者自己使用，别的用户不能使用）、CREATE PUBLIC DATABASE LINK（PUBLIC表示所创建的DBLINK所有用户都可以使用）与DROP PUBLIC DATABASE LINK（删除公共的DBLINK）。可以使用如下授权语句给用户SCOTT授予CREATE PUBLIC DATABASE LINK和DROP PUBLIC DATABASE LINK两个权限：
GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK TO SCOTT;
查询数据库的DBLINK可以使用SQL语句：SELECT * FROM DBA_DB_LINKS。
DBLINK的分类如下表所示：
类型 | 用户 | 描述 |
|---|---|---|
Private | 创建database link的user拥有该database link | 在本地数据库的特定的schema下建立的database link。只有建立该database link的schema的用户才能使用这个database link来访问远程的数据库。同时也只有创建者才能删除它自己的private database link。 |
Public | Owner是PUBLIC | Public的database link是数据库级的，本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此database link来访问相应的远程数据库。 |
Global | Owner是PUBLIC | Global的database link是网络级的。此时的global_names必须设置为true。 |
database link用户验证方法包括3种，分别为current user、fixed user和connected user，如下表所示：
创建命令 | 用户验证方式 |
|---|---|
不指定 | 默认值采取Connected User的验证方法 |
CONNECT TO CURRENT_USER | 采取CURRENT_USER的验证方式 |
CONNECT TO user_name IDENTIFIED BY password | 采取Fiexed User的验证方式 |
举例如下：
DBLINK的创建命令 | 用户验证方式 |
|---|---|
CREATE database link DBL_sales USING 'sales_us'; | Private connected user |
CREATE database link DBL_foo CONNECT TO CURRENT_USER USING 'am_sls'; | Private current user |
CREATE database link DBL_salesCONNECT TO scott IDENTIFIED BY tiger USING 'sales_us'; | Private fixed user |
CREATE PUBLIC database link DBL_sales CONNECT TO scottIDENTIFIED BY tiger USING 'rev'; | Public fixed user |
CREATE SHARED PUBLIC database link DBL_salesCONNECT TO scott IDENTIFIEDBY tiger AUTHENTICATED BYanupam IDENTIFIED BY bhide USING 'sales'; | Shared public fixed user |
创建DBLINK一般有两种方式，第一种方式是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库的TNS，例如这里配置了TNS_BJLHR，然后创建公共DBLINK：
CREATE PUBLIC DATABASE LINK
DBL_BJLHR CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TNS_BJLHR';
其中，DBL_BJLHR是创建的DBLINK名字，SCOTT/TIGER是登录到远程数据库的用户名/密码，然后在本地数据库中通过DBLINK访问远程数据库“TNS_BJLHR”中SCOTT.TB_TEST表，SQL语句如下所示：
SELECT * FROM SCOTT.TB_TEST@DBL_BJLHR;
创建DBLINK的第二种方式，是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库的时候，而直接将相关的内容写到DBLINK的配置中，如下所示：
CREATE DATABASE LINK DBL_BJLHR
CONNECT TO SCOTT IDENTIFIED BY TIGER
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BJLHR)
)
)';
其中，HOST为数据库的IP地址，SERVICE_NAME为数据库的SID，其实，两种方法配置DBLINK是差不多的，第二种方法不受本地服务的影响。
另外，当数据库参数GLOBAL_NAMES为TRUE时，要求数据库链接名称跟远端数据库名称一样。数据库全局名称可以用以下命令查出：
SELECT * FROM GLOBAL_NAME;
用以下语句来修改参数值：
ALTER SYSTEM SET GLOBAL_NAMES=FALSE/TRUE;
当数据库参数GLOBAL_NAMES为FALSE时，就不要求数据库链接名称跟远端数据库名称一样。
需要注意的是，对于Private类型的dblink，只有其创建者才能删除dblink。如果是Public的dblink，那么只要有“DROP PUBLIC DATABASE LINK”的权限就可以删除dblink：
SYS@PROD1> create database link sh.prod2_2 connect to sh identified by sh using 'PROD2';
Database link created.
SYS@PROD1> COL DB_LINK FORMAT A10
SYS@PROD1> COL HOST FORMAT A10
SYS@PROD1> COL OWNER FORMAT A10
SYS@PROD1> COL USERNAME FORMAT A10
SYS@PROD1> select * from dba_db_links d WHERE D.db_link LIKE '%PROD2_2%';
OWNER DB_LINK USERNAME HOST CREATED
---------- ---------- ---------- ---------- ---------
SYS SH.PROD2_2 SH PROD2 15-DEC-17
SYS@PROD1> conn sh/sh
Connected.
SH@PROD1> create database link prod2 connect to sh identified by sh using 'PROD2';
Database link created.
SH@PROD1> conn / as sysdba
Connected.
SYS@PROD1> drop database link sh.PROD2;
drop database link sh.PROD2
*
ERROR at line 1:
ORA-02024: database link not found
& 说明：
有关Oracle Dblink的更多内容介绍可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2148696/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。