【DB筆試面試520】在Oracle中，Oracle的DBLINK是什麼？
- 2019 年 10 月 8 日
- 筆記
♣
題目部分
在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程序員面試筆試寶典》，作者：李華榮。
