9.11 Database Link

Oracle Database Link (DB Link) 簡單來說，就是在兩個不同的 DB 之間建立起一個橋樑，提供了跨資料庫查詢的一個途徑，例如有兩個資料庫 DB1 與 DB2 ，由 DB1 建立一個 DB Link 到 DB2 ，那麼就可以由 DB1 發起一條查詢的指令來查詢 DB2 的 Table :

Database Link 的原理是透過 Tnsnames 來連線到另一個資料庫，換句話說，我們建立 DB Link 其實就是在告訴系統如何連線到另一個資料庫，由系統自動幫我們連線到另一個資料庫並且把資料撈取回來。 DB Link 可以分為 Public Database Link 與 Private Database Link 兩種， Public Database Link 表示此資料庫的所有使用者都可以使用；而 Private Database Link 只有建立此 DB Link 的使用者可以使用。使用 create database link 的語法來建立:

SQL> create public database link ora12 connect to hr identified by hr using 'ORA12';       (建立 public DB Link) SQL> create database link ora12 connect to hr identified by hr using 'ORA12';       (建立 private DB Link)

其中:

• connect to : 表示連線到另一個資料庫的使用者名稱。

• identified by : 表示連線使用者的密碼。

• using : 表示連線到另一個資料庫的 Tnsnames ，需要先在 $ORACLE_HOME/network/admin/tnsnames.ora 裡面設定。

這邊要注意的是，只有在後面的 using 才需要使用單引號，前面輸入帳號密碼不需要用單引號。如果要建立的是 Public Database Link 只需要加上 public 關鍵字就可以了。建立好 DB Link 之後，我們就可以使用 @ 語法來查詢另一個資料庫的資料，例如查詢 ORA12 資料庫的 employees :

SQL> select * from employees@ORA12;

要得知目前資料庫有多少個 Database Link ，使用 dba_db_links 來查詢:

SQL> select * from dba_db_links;

Database Link 與資料庫的 Global Name 有著一定的關係， Global Name 預設為 db_name.db_domain ，在資料庫建立時如果有設定 db_domain 參數，那麼 Global Name 就會自動設定為 db_name.db_domain ，如果在創建時沒有設定 db_domain ，那麼即便後來設定了 db_domain ， Global Name 也不會自動變更，例如 ORA11 這個資料庫在建立時設定 db_domain 為 oracle.com ，那麼它的 Global Name 就為 ORA11.oracle.com 。

當參數 global_names 設定為 TRUE 的時候，此時所建立 DB Link 的名稱就會帶上 db_domain ，並且此 DB Link 的命名必須與所連線資料庫的 Global Name 名稱相同才能夠成功的連線，例如目前資料庫的 Global Name 為 ORA11.ORACLE.COM ，那麼所建立的 DB Link 也會自動帶上 ORACLE.COM :

雖然成功建立了，但很有可能還是會連線失敗:

這個訊息說明 DB Link 的名稱為 ORA12.ORACLE.COM 但是對方資料庫的 Global Name 為 ORA12 ，這兩者是不相同的，所以無法成功連線過去，此時必須要把 ORA12 這個資料庫的 Global Name 改為 ORA12.ORACLE.COM 才可以成功的連線:

這邊順帶提一下，更改 Global Name 的方式有兩種，一種是使用 alter database rename global_name ，一種是直接 update sys.PROPS$ :

SQL> alter database rename global_name to ORA11.ORACLE.COM;       (更改 global name 為 ORA11.ORACLE.COM) SQL> update sys.PROPS$ set value$='ORA11' where name='GLOBAL_DB_NAME'; SQL> commit;       (更改 global name 為 ORA11)

總結來說，當參數 global_names 為 TRUE 的時候， DB Link 的名稱必須使用對方資料庫的 Global Name ，當 Global Name 帶有 db_domain 時， DB Link 的名稱也會自動帶上 db_domain 。建議使用 DB Link 這項功能時， global_names 使用預設的 FALSE ，這樣名稱的設定會比較直觀，我們想要替 DB Link 取甚麼名稱就取甚麼，也不用考慮到對方資料庫的 Global Name ，用起來也不會有 db_domain 的問題。最後要注意的是，當 DB Link 建立之後，就不可以隨意地更改 global_names 參數或是 Global Name ，否則會造成系統無法辨識已經存在的 DB Link 名稱，會出現如下情況，明明可以查詢的到，但是系統卻無法辨識:

若要驗證 DB Link 的連線是否可行，最簡單的方式就是執行一段 select 能否成功，問題就在於 Private Database Link 必須要以 DB Link 的擁有者登入才可以進行測試連線，其它使用者是無法驗證 Private Database Link 的連線的:

 

此時可以藉由建立下列 Procedure 來測試 Private Database Link ，把這個 Procedure 建立在 Private Database Link 的擁有者底下，以這個例子來說就是建在 HR 底下來驗證 priv_ora12 這個 Private Database Link :

create or replace procedure hr.dblink_test(link_name varchar2) is type lnkcurtyp is ref cursor; cur_link_name lnkcurtyp; glname varchar2(30); begin  open cur_link_name for 'select * from global_name@'||link_name;  loop   fetch cur_link_name into glname;   exit when cur_link_name%notfound;   dbms_output.put_line('GL_NAME : '||glname);  end loop;  close cur_link_name; end; /

透過這個 Procedure ，即便不是使用 DB Link 擁有者也可以驗證 Private Database Link 是否可以連線:

最後要提的是，從 Oracle 11.2.0.4 開始，增加了安全性，已經無法查得 DB Link 連線使用者的密碼了:

 

而 DB Link 的建立又要求輸入連線使用者的名稱與密碼，那麼要怎麼移轉 DB Link 的設定 ? 這個時候就必須使用 Data Pump 將 DB Link 的設定 export 再 import 到目標端，使用這種方式就可以避免掉帳號密碼的問題，執行 expdp 時使用 full=y 與 include=db_link 就可以單獨匯出 Database Link :

雖然 Database Link 可以跨越資料庫來查詢資料，但畢竟它還是透過網路連線把資料撈回來，建議不要使用在太複雜的 SQL 查詢上，例如使用 DB Link 進行過多的 join 或是子查詢，這種情況當效能出現問題時會不容易進行優化。