Oracle DBLINk的使用

作用:将多個不同地點的伺服器的oracle資料庫邏輯上看成一個資料庫,也就是說在一個資料庫中可以操作另一個遠端的資料庫中的對象。 

文法:

CREATE [PUBLIC] DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING ‘connect_string‘;

DROP [PUBLIC] DATABASE LINK dblink; 

    注意:你必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的權限（可用sys身份去配置設定）,另外,在你要連接配接的資料庫上的權限.

grant CREATE DATABASE LINK to hr;

參數說明: 

    dblink: 你所建立的database link的名字, 

    user和password:要連接配接的資料庫的使用者名和密碼 

    connect_string:可以是經過Net Manager配置的(tnsnames.ora)且經測試可以連接配接的服務名，不過也更直接用tnsnames裡的字元串：(DESCRIPTION =

(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =orcl) )

通過SHOW PARAMETER GLOBAL_NAMES，可以檢視到其值是FALSE或者TRUE。

一）對于GLOBAL_NAMES=FALSE的情況，則DBLINK的名稱可以自定義，相關的過程如下：

實作從本地資料庫連接配接到遠端資料庫伺服器：

遠端伺服器要配置監聽并且啟動它

本地伺服器要配置tnsnames

 實作在本地伺服器上連接配接到遠端伺服器資料庫：

在本地資料庫上，建立連接配接：

1、 連結字元串即服務名，首先在本地配置一個服務名，位址指向遠端的資料庫位址,當然也直接寫的連接配接字元串。 

2、建立資料庫連結（前提是已配置設定相應權限），

SQL> grant CREATE DATABASE LINK to hr;

Grant succeeded.

SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';

Database link created.

當然也可以直接寫連接配接字元串

SQL>create database link LinkRemoteTestDB2 connect to hr identified by hr

using 'TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )';

則建立了一個以hr使用者和TEST資料庫的連結LinkRemoteTestDB.

3.使用database link來查詢遠端HR schema下的testdblink 表資訊

SQL> select * from [email protected];

        ID NAME

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

         1 Watson

         2 John

通過執行select * from [email protected]; 進行測試，結果OK

二）對于GLOBAL_NAMES = TRUE的情況，資料庫連結（DATABASE LINK）的名字必須和資料庫的名字相同：

在本地伺服器上執行下面語句使GLOBAL_NAMES=TRUE：

SQL>ALTER SYSTEM SET GLOBAL_NAMES=TRUE;

再查詢時，會有如下的錯誤：

SQL> select * from [email protected];

select * from [email protected]

                         *

ERROR at line 1:

ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM

connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

登入遠端資料庫，通過執行

SQL>SELECT * FROM GLOBAL_NAME;得到其資料庫全名為TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

用原方式SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';建立過程不會出錯，但執行“select * from

[email protected];”的時候，就會出現ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM

connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM的錯誤了 

是以需要采用下面的方式建立DBLINK:

SQL> create database link TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to HR identified by HR using 'TEST';

Database link created.再次執行

SQL> select * from [email protected];

        ID NAME

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

         1 Watson

         2 John

SQL> UPDATE [email protected] t set t.name='WatsonModified' where id=1;

1 row updated.

SQL> select * from [email protected];

        ID NAME

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

         1 WatsonModified

         2 John

執行成功了！！！！！

DBLINK其他相關的知識：

1、檢視所有的資料庫連結

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

SQL>select * from dba_db_links;

2、删除資料庫連接配接

SQL> drop database link LinkRemoteTestDB;

Database link dropped.

例子：

create public database link tblink

　　connect to EM identified by em123456

　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 172.24.105.70)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

select * FROM [email protected];

exp_requisition_headers

drop public database link tblink;