Oracle dblink詳解

database link是定義一個資料庫到另一個資料庫的路徑的物件，database link允許你查詢遠端表及執行遠端程式。在任何分散式環境裡，database都是必要的。另外要注意的是database link是單向的連線。

在建立database link的時候，Oracle再資料字典中儲存相關的database link的資訊，在使用database link的時候，Oracle透過Oracle Net用使用者預先定義好的連線資訊訪問相應的遠端資料庫以完成相應的工作。

建立database link之前需要確認的事項：

確認從local database到remote database的網路連線是正常的，tnsping要能成功。

確認在remote database上面有相應的訪問許可權。

database link分類

型別 Owner 描述 Private

建立database link的user擁有該database link 在本地資料庫的特定的schema下建立的database link。只有建立該database link的schema的session能使用這個database link來訪問遠端的資料庫。同時也只有Owner能刪除它自己的private database link。

Public Owner是PUBLIC. Public的database link是資料庫級的，本地資料庫中所有的擁有資料庫訪問許可權的使用者或pl/sql程式都能使用此database link來訪問相應的遠端資料庫。

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 private database link. CREATE PUBLIC DATABASE LINK Local Creation of a public database link. CREATE SESSION Remote Creation of any type of database link.

database link的使用

基本語法

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端的資料庫GLOBAL_NAME=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時，需要如果採用資料字典驗證，則需要兩邊資料庫的使用者名稱密碼一致。

建立database link選項說明

取值 說明 不指定 預設值建立一個dedicated的連線，每一個使用database link的本地session都會對應有一個遠端資料庫的session。 SHARED 建立一個共享的資料庫連線，同時要指定database link_authentication。

使用shared方式的 database link是資料庫會限制到遠端資料庫的連線的數量，這樣以避免過多的連線對遠端資料庫造成太大的壓力。在使用shared database link的時候，到database link的連線會在連線以後與本地連線斷開，為防止未授權的session使用此連結而要求在建立shared database link的時候必須要指定database link_authentication。

(from Oracle document: A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.)

共享連結更多資料

共享資料庫連結是指該連結的多個使用者可以共享同一個底層網路連線。例如，在有四位使用者的MTS（多執行緒伺服器）環境下，每一個共享伺服器程式都將與遠端伺服器有一個物理連結，這四位使用者共享這兩個連結。

表面上，共享連結乍一聽起來像是一件好事。在某些環境下的確如此，但是，當你考慮使用共享連結時，應當意識到這有許多侷限性和警告：

如果你使用一個專用的伺服器連線來連線到你的本地資料庫，連結只能在你從那些連線中建立的多重會話間共享 。在MTS環境裡，每一個共享伺服器程式潛在地開啟一個連結。所有的會話被同一共享伺服器程式提供並且分享被那個程式開啟的任意共享連結。因為在MTS環境裡的一個共享伺服器程式能夠服務於許多使用者連線，共享連結的使用可能導致開啟的連結遠多於所必須的連結。用SHARED關鍵字建立共享資料庫連結。還必須使用AUTHENTICATED BY 子句在遠端系統上指定一有效的使用者名稱和口令。如下命令建立一個共享的、公用的、連線使用者資料庫連結：

    CREATE SHARED PUBLIC database link GNIS

    AUTHENTICATED BY DUMMY_USER IDENTIFIED BY SECRET

    USING ‘GNIS’;

要獲得建立連結和管理分散式系統的更多資料，請查閱Oracle Technology Network ()。

使用AUTHENTICATED BY子句稍微有些困擾，但是由於實現共享連結的方式安全性決定它是必須的。這個例子中的使用者名稱和口令DUMMY_USER/SECRET必須在遠端系統上有效。然而，遠端系統上使用的帳戶仍就是連線使用者的帳戶。如果我以JEFF/SECRET登陸到我的本地資料庫並使用我剛建好的共享連結，將會發生以下一系列事件：

為了開啟連結，Oracle使用DUMMY_USER/SECRET向遠端資料庫授權。然後，Oracle試圖使用HMAD/SECRET使我登陸到遠端資料庫。共享連結的主要目的是減少兩個資料庫伺服器之間的底層網路連線數量。它們最適合於MTS環境，在那你擁有大量的透過這一連結訪問遠端資料庫的使用者。觀念上，你想讓使用者數量超過共享伺服器程式的數量。那麼你可以透過為每一共享伺服器程式開啟一個連結而不是每位使用者開啟一個連結的方法，節省資源。

database link使用方式

取值 說明 不指定 預設值建立一個private的database link PUBLIC 公共連線，這樣的連線可以被資料的所有的使用者訪問 database link使用者驗證方法 取值 說明 不指定 預設值採取Connected User的驗證方法 CONNECT TO CURRENT_USER 採取CURRENT_USER的驗證方式 CONNECT TO user_name IDENTIFIED BY password 採取Fiexed User的驗證方式 database link建立舉例 SQL Statement Connects To Database Connects As Link Type CREATE database link

sales.us.americas.acme_auto.com USING ‘sales_us’; sales using net service name sales_us Connected user Private connected user CREATE database link foo

CONNECT TO CURRENT_USER USING

‘am_sls’; sales using service name am_sls Current global user Private current user CREATE database link

sales.us.americas.acme_auto.com

CONNECT TO scott IDENTIFIED

BY tiger USING ‘sales_us’; sales using net service name sales_us scott using password tiger Private fixed user CREATE PUBLIC database link

sales CONNECT TO scott

IDENTIFIED BY tiger USING ‘rev’; sales using net service name rev scott using password tiger Public fixed user CREATE SHARED PUBLIC database link

sales.us.americas.acme_auto.com

CONNECT TO scott IDENTIFIED

BY tiger AUTHENTICATED BY

anupam IDENTIFIED BY bhide USING ‘sales’; sales using net service name sales scott using password tiger, authenticated as anupam using password bhide Shared public fixed user

不使用TNS Name一例：

CREATE database link link_name

CONNECT TO user IDENTIFIED BY screct

USING '(DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = sales.company.com)(PORT = 1521))

    )

    (CONNECT_DATA =

        (SERVICE_NAME = sales)

    )

)';

database link 的使用

-- 最簡單的用法

SELECT * FROM table_name@database link;

-- 不想讓使用的人知道database link的名字的時候

-- 建一個別名包裝一下

CREATE SYNONYM table_name FOR table_name@database link;

SELECT * FROM table_name;

-- 或者，也可以建立一個檢視來封裝

CREATE VIEW table_name AS SELECT * FROM  table_name@database link;

database link刪除

-- 刪除public型別的database link

DROP PUBLIC database link link_name;

-- 刪除非public型別的database link

-- 注意：只有owner自己能刪除自己的非public型別database link

DROP database link link_name;

檢視database link的資訊

檢視系統database link的基本資訊

DBA_DB_LINKS (ALL_DB_LINKS/USER_DB_LINKS)

DBA_DB_LINKS檢視為每一定義的連結返回一行。OWNER 列和DB_LINK列分別顯示了這一連結的所有者及名稱。對公用資料庫連結，OWNER列將包含’PUBLIC’。如果你建立固定使用者連結，使用者名稱應在DBA_DB_LINKS檢視的USERNAME列裡。ALL_DB_LINKS 檢視和 USER_DB_LINKS檢視與 DBA_DB_LINKS檢視相類似-它們分別顯示了你能夠訪問的所有連結及你所擁有的全部連結。

COL OWNER FOR A15

COL DB_LINK FOR A25

COL HOST FOR A25

COL USERNAME FOR A15

SELECT * FROM DBA_DB_LINKS;

DBA_OBJECTS (ALL_OBJECTS/USER_OBJECTS)

在這個檢視裡面只能查詢到系統有那些database link以及他們的owner，建立時間等資訊。

COL OWNER FOR A15

COL OBJECT_NAME FOR A25

COL OBJECT_TYPE FOR A25

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE LIKE 'database link';

檢視儲存的Fixed user database link所儲存的使用者密碼,該密碼是經過加密的。

這是比較危險的一件事，有必要對錶sys.link$的許可權進行限制。

col host for a15

col userid for a15

col passwordx for a40

col name for a20

select name, host, userid, passwordx from sys.link$;

透過database link去SELECT遠端資料庫的一個表也是一個事務嗎？

select * from v$transaction;

 -- 沒有記錄，說明沒有事務

 -- 透過database link連線遠端資料庫，select 其中一個表

 select * from bbs_news@mylink

 select * from v$transaction;

 -- 發現有一條記錄。

解釋

    因為本地資料庫只是將對應的sql傳送給遠端資料庫執行，接受remote db返回的結果，但他並不知道是否該sql修改了資料； 所以需要為select 語句也標示一個事務。

    具體可以參考otn 分散式資料庫手冊，所以在用database link遠端訪問時,要加上set transaction read only;

close database link

ALTER SESSION CLOSE database link sales;

其實，dblink的相應屬性對應了Oracle的資料字典link$,任何針對dblink的操作都是操作該資料字典。在9i的時候，如果Oracle的global_name僅包括db_name，也就是說DB_DOMAIN的值為空。那麼這個時候建立的資料庫鏈，在資料庫修改全域性名GLOBAL_NAME之後（修改為db_name.db_domain格式），會無法刪除。

如果要產生資料庫鏈，必須將GLOBAL_NAME改回DB_NAME格式，即去掉後面的DOMAIN，但是這個時候，RENAME操作會自動新增域名，使得Oracle全域性名無法恢復到初始狀態。因此在這情況下，如果需要刪除dblink，只能直接操作link$資料字典

delete from link$ where owner#=user_id and name=dblink_name

當然直接運算元據字典是危險的，最好做好備份，然後再進行操作。

利用dblink執行ddl

我們知道任何ddl語句都無法在dblink中直接執行，示例如下

SQL> desc db_test;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)

 

SQL> drop table db_test@DBLINK_CONNECTED_HR;

drop table db_test@DBLINK_CONNECTED_HR

                   *

ERROR at line 1:

ORA-02021: DDL operations are not allowed on a remote database

透過建立儲存過程，使得能在dblink中執行ddl語句。 注意需在目標資料庫的相應使用者下建立儲存過程 ，具體如下

SQL> create or replace procedure p_execute_ddl(p_ddl in varchar2)

  2  as

  3  begin

  4  execute immediate p_ddl;

  5  end;

  6  /

刪除目標資料庫的表

SQL> exec p_execute_ddl@DBLINK_CONNECTED_HR('drop table db_test');

PL/SQL procedure successfully completed.

 

SQL> desc db_test;

ERROR:

ORA-04043: object db_test does not exist

dblink的限制

You cannot perform the following operations using database links:

Grant privileges on remote objects

Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:

        Tables

        Views

        Procedures

        Functions

Analyze remote objects

Define or enforce referential integrity

Grant roles to users in a remote database

Obtain nondefault roles on a remote database. For example, if jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as scott, jane receives scott's default roles on the remote database. Jane cannot issue SET ROLE to obtain a nondefault role.

Execute hash query joins that use shared server connections

Use a current user link without authentication through SSL, password, or NT native authentication

本文內容轉自    作者：czmmiao