dblink:基本信息,分類,基本語法,實例,

dblink(Database Link)資料庫連結顧名思義就是資料庫的連結，就像電話線一樣，是一個通道，當我們要跨本地資料庫，訪問另外一個資料庫表中的數據時，本地資料庫中就必須要創建遠程資料庫的dblink,通過dblink本地資料庫可以像訪問本地資料庫一樣訪問遠程資料庫表中的數據。

基本介紹

外文名：dblink
含義：資料庫的連結
許可權： PUBLIC DATABASE LINK系統許可權
連線字元串：connectstring

基本信息,分類,基本語法,實例,

基本信息

格式

1.創建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']

說明：

1)許可權：創建資料庫連結的帳號必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統許可權，用來登錄到遠程資料庫的帳號必須有CREATE SESSION許可權。這兩種許可權都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK許可權在DBA中）。一個公用資料庫連結對於資料庫中的所有用戶都是可用的，而一個私有連結僅對創建它的用戶可用。由一個用戶給另外一個用戶授權私有資料庫連結是不可能的，一個資料庫連結要么是公用的，要么是私有的。

2)link : 當source端的參數(parameter)GLOBAL_NAMES=TRUE時，link名必須與遠程資料庫的全局資料庫名global_name）相同；否則，可以任意命名。

3)current_user使用該選項是為了創建global類型的dblink。在分散式體系中存在多個資料庫的話。如果想要在每一個資料庫中都可以使用同樣的名字來訪問資料庫a，那在每個資料庫中都要創建一個到資料庫a的db_link，太麻煩了。所以有這個選項的話你只要創建一次。所有的資料庫都可以使用這個db_link來訪問了。要使用這個特性，必須有oracle nameserver或者ORACLE目錄伺服器。並且資料庫a的參數global_names=true.具體我也沒有創建過，沒有這個環境。

4)connectstring：連線字元串，tnsnames.ora中定義遠程資料庫的連線串，也可以在創建dblink的時候直接指定。

5)username、password：遠程資料庫的用戶名，口令。如果不指定，則使用當前的用戶名和口令登錄到遠程資料庫，當創建connected user類型的dblink時，需要如果採用數據字典驗證，則需要兩邊資料庫的用戶名密碼一致。

分類

dblink的類型及擁有者

類型	Owner	描述
Private	創建dblink的user擁有該dblink	在本地資料庫的特定的schema下建立的databaselink。只有建立該 databaselink的schema的session能使用這個databaselink來訪問遠程的資料庫。同時也只有Owner能刪除它自己的privatedatabaselink。
Public	Owner是PUBLIC.	Public的databaselink是資料庫級的，本地資料庫中所有的擁有資料庫訪問許可權的用戶或pl/sql程式都能使用此databaselink來訪問相應的遠程資料庫。
Global	Owner是PUBLIC.	Global的database link是網路級的，When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

dblink創建所需許可權

Privilege	Database	Required For
CREATE DATABASE LINK	Local	Creation of a privase database link.
CREATE PUBLIC DATABASE LINK	Local	Creation of a public database link.
CREATE SESSION	Remote	Creation of any type of database link.

基本語法

創建dblink

創建dblink一般有兩種方式，不過在創建dblink之前用戶必須有創建dblink的許可權。想知道有關dblink的許可權，以sys用戶登錄到本地資料庫：

select * from user_sys_privs t
where t.privilege like upper('%link%');

查詢結果集：
1 SYS CREATE DATABASE LINK NO
2 SYS DROP PUBLIC DATABASE LINK NO
3 SYS CREATE PUBLIC DATABASE LINK NO
可以看出在資料庫中dblink有三種許可權：

1.CREATE DATABASE LINK（所創建的dblink只能是創建者能使用，別的用戶使用不了），
2.CREATE PUBLIC DATABASE LINK（public表示所創建的dblink所有用戶都可以使用），
3.DROP PUBLIC DATABASE LINK。
在sys用戶下，把CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASELINK許可權授予給你的用戶：

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;

然後以scott用戶登錄本地資料庫

1、已經配置本地服務
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里定義.
2、直接建立連結
create database link link_name
connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SSID)
)
)';
host=資料庫的ip地址，service_name=資料庫的ssid。
其實兩種方法配置dblink是差不多的，個人感覺還是第二種方法比較好，這樣不受本地服務的影響。
注意: 假如創建全局dblink，則必須使用systm或sys用戶，在database前加public。

dblink查詢

查看所有的資料庫連結，進入系統管理員SQL>操作符下，運行命令：

SQL>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）起作用的，其他用戶無法使用這個連線，無許可權也不能刪除它。

實例

需要注意的是在oracle參數中，有一個參數叫global_names，如果該參數為TRUE，那么在使用db link時，db link的名字一定要和被訪資料庫實例名一致，否則會報ORA-2085錯。

Machine 1：
IP：192.168.100.162
ORACLE_SID：HX
Machine 2：
IP：192.168.100.4
ORACLE_SID：prepaid
1．在Machine 2的資料庫中增加用戶
以oracle用戶登錄Machine 2
$export ORACLE_SID=prepaid
$sqlplus / as sysdba
SQL>create user test identified by test;
User created.
SQL>grant connect,resource to test;
Grant succeeded.
2．修改Machine 1的tnsnames.ora檔案
增加如下內容：
prepaid_test=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = prepaid)
)
)
3．創建db link

以oracle用戶登錄Machine 1
$export ORACLE_SID=HX
$sqlplus / as sysdba
SQL>create public database link prepaid_test connect to test identified by test using 'prepaid_test';
Database link created.
SQL>show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL>select * from dual@prepaid_test;
select * from dual@prepaid_test
*
ERROR at line 1:
ORA-02085: database link PREPAID_TEST connects to PREPAID
SQL>alter system set global_names=false;
System altered.
SQL>select * from dual@prepaid_test;

D
-
X
可以看到，當global_names=false時，db link可以連線遠程資料庫。
$oerr ora 2085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
// The connection is rejected.
// *Action: create a database link with the same name as the database it
// connects to, or set global_names=false.
//

4．修改Machine 1的tnsnames.ora檔案
修改prepaid_test為prepaid：
prepaid =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = prepaid)
)
)
5．重新創建db link
以oracle用戶登錄Machine 1
$export ORACLE_SID=HX
$sqlplus / as sysdba
SQL>drop public database link prepaid_test;
Database link dropped.
SQL>create public database link prepaid connect to test identified by test using 'prepaid';
Database link created.
SQL>alter system set global_names=true;
System altered.
SQL>show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL>select * from dual@prepaid;

D
-
X
可以看到，當global_names=true時，如果創建的db link名字與遠程資料庫的實例名一致，還是可以訪問的。

dblink

基本介紹

基本信息

分類

基本語法

實例

相關詞條

熱門詞條