Oracle DATABASE LINK(DBLINK)建立

Oracle DATABASE LINK(DBLINK)建立

資料庫全域名稱可以用以下命令查出：

SELECT * FROM GLOBAL_NAME;

修改可以用以下語句來修改參數值：

ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;

<1>、當資料庫參數global_name=false時，就不要求資料庫連結名稱跟遠端資料庫名稱一樣。

Oracle資料庫之間進行串連通訊。
建立資料庫連結的文法如下：
CREATE [PUBLIC] DATABASE LINK link

CONNECT TO username IDENTIFIED BY password

USING ‘connectstring’

 

注意：建立資料庫連結的帳號必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統許可權，用來登入到遠端資料庫的帳號必須有CREATE SESSION許可權。這兩種許可權都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK許可權在DBA中）。

一個公用資料庫連結對於資料庫中的所有使用者都是可用的，而一個私人連結僅對建立它的使用者可用。由一個使用者給另外一個使用者授權私人資料庫連結是不可能的，一個資料庫連結要麼是公用的，要麼是私人的。

建立資料庫連結時，還可以使用預設登入方式，即不指定遠端資料庫的使用者名稱和密碼：

create public database link zrhs_link

using ‘zrhs’;

在不指定使用者名稱和口令的情況下，ORACLE使用當前的使用者名稱和口令登入到遠端資料庫。

USING後面指定的是連結字串，也就是遠端資料庫的網路服務名，這個服務名儲存在本地TNSNAMES.ORA檔案中，在該檔案中定義了協議、主機名稱、連接埠和資料庫名。

刪除資料庫連結的語句是：

DROP [PUBLIC] DATABASE LINK zrhs_link

資料庫連結的引用

一般情況下引用資料庫連結，可以直接將其放到調用的表名或視圖名稱後面，中間使用一個 @ 作為分割符：

SELECT * FROM worker@zrhs_link;

對於經常使用的資料庫連結，可以建立一個本地的同義字，方便使用：

CREATE SYNONYM worker_syn FOR worker@zrhs_link;

還可以建立一個本地的遠程視圖，方便使用：

CREATE VIEW worker AS SELECT * FROM worker@zrhs_link where… ;

現在本視圖可與本機資料庫中的任何其它視圖一樣對待，也可以授權給其它使用者，訪問此視圖，但該使用者必須有訪問資料庫連結的許可權。

對於另外一種情況，所要訪問的表不在資料庫連結中指定的遠程帳戶下，但該帳戶有訪問該表的許可權，那麼我們在表名前要加上該表的使用者名稱：

SELECT * FROM camel.worker@zrhs_link ;

<2>、當資料庫參數global_name=true時，那就要求資料庫連結名稱跟遠端資料庫全域名稱一樣

資料庫全域名稱可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

建立dblink的文法：
sql>create database link 資料庫鏈路名
connect to 使用者名稱 identified by 口令
using '主機字串名';
如：
sql>create database link ora9i.us.oracle.com ### 這裡的us.oracle.com為oracle預設網域名稱 ###
connect to scott identified by tiger
using 'sun';

1)dblink名必須與遠端資料庫的全域資料庫名（global_name）相同；
2)使用者名稱，口令為遠端資料庫使用者名稱，口令;
3)主機字串為本機tnsnames.ora中定義的串;
4)兩個同名的資料庫間不得建立dblink;
然後，你就可以通過dblink訪問遠端資料庫了。
如：
sql>select * from 表名@ora9i.us.oracle.com;
還可以建立快照(snapshot)通過dblink實現遠端資料自動傳輸。

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

SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';

 

下面對第二種情況進行舉例說明：

北京的總部有一個集中的資料庫，其SID是SIDBJ，使用者名稱：userbj,密碼：bj123,北京的IP地址是：192.168.1.101。
在本地(新疆)的分公司也有一個資料庫，其SID是SIDXJ，使用者：userxj，密碼：xj123，新疆的IP地址是：192.168.1.100。
要將本地新疆的SIDXJ資料庫中訪問到北京的資料庫SIDBJ中的資料。
也就是說，在sidxj的資料庫中，使用者userxj(192.168.1.100)需要建立DBLINK，以userbj的使用者身份訪問sidBJ(192.168.1.101)中的資料。

測試環境：兩個資料庫均建立在WINXP上，ORACLE的版本均為Oracle817
建立環境時，要注意關閉兩台電腦上的Windows的防火牆，否則，會出現能ping通，但Oracle串連不通的情況。

1、問：如何返回資料庫的GLOBAL_NAME？
執行SELECT * FROM GLOBAL_NAME;
北京的資料庫的GLOBAL_NAME為SIDBJ.US.ORACLE.COM
新疆的資料庫的GLOBAL_NAME為SIDXJ

2、問：如何查看Global_name參數是true還是False?
答：執行：SQL> show parameter global_name;
執行的結果如下：
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                        boolean    TRUE

表示該參數是true.

也可以通過查看v$option視圖，如果其中Advanced replication為TRUE，則支援進階複製功能；否則不支援。
執行語句為：select * from v$option;

3、問：如何檢查Oracle的版本是否支援同步功能？
答：執行select * from v$option where PARAMETER='Advanced replication'語句，
如何傳回值為True，那麼就是支援，否則就是不支援。在兩個資料庫中都是檢查是否支援才行。

建立步驟：

1、在本地建立一個Oracle的用戶端串連tns_xj_to_bj，用於串連北京的資料庫。
2、建立一個串連tnsxj，用於串連本地的sidxj資料庫，以tnsxj/userxj/xj123登入到PLSQL中。

下面開始建立串連到北京的遠端資料串連DBLink。
create database link SIDBJ.US.ORACLE.COM connect to userbj identified by bj123 using 'tns_xj_to_bj';
其中：SIDBJ.US.ORACLE.COM是遠端資料庫的global_name,userbj是串連SIDBJ的使用者名稱，bj123是userbj的密碼，
tns_xj_to_bj是本地建立的串連到北京的資料庫的服務名。

3、測試連接是否成功：
select * from dual@SIDBJ.US.ORACLE.COM
如果返回結果如下則表示串連成功了。
DUMMY
-----
X

4、查詢已經建立的遠端連線名：
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                          OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM                        SIDBJ.US.ORACLE.COM
USERXJ                        DBLINK_XJ_TO_BJ
USERXJ                        SIDBJ

5、至此，在新疆的電腦上建立了一個DBLINK，用於串連到北京的資料庫上，
在北京的資料庫中，建立一個表用於測試。
create table USERBJ.BJ_TEST
(
STU_ID  NUMBER,
STU_NAME VARCHAR2(100)
)
在其中增加一條記錄：
insert into BJ_TEST (STU_ID, STU_NAME)
values (1, '鐘德榮');

在新疆的資料庫中查詢北京的資料庫中表的資訊：
select * from bj_test@SIDBJ.US.ORACLE.COM
查詢結果：
    STU_ID STU_NAME
---------- --------------------------------------------------------------------------------
        1 鐘德榮

表示查詢是正常的。
說明：該查詢是用

以上指令碼全部通過測試。