Oracle dblink介紹

---

---

官網：

---

---

當使用者要跨本地資料庫訪問另外一個資料庫表中的資料時，本地資料庫中必須建立了遠端資料庫的DBLINK，透過DBLINK可以像訪問本地資料庫一樣訪問遠端資料庫表中的資料。其實，DBLINK和資料庫中的VIEW差不多，建立DBLINK的時候需要知道待讀取資料庫的IP地址、ORACLE_SID以及資料庫使用者名稱和密碼。

在建立DBLINK之前，普通使用者必須具有相關的許可權才能建立DBLINK，以SYS使用者登入到本地資料庫可以看到相關的許可權：

SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');

SYS@PROD1> SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');


USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SYS                            CREATE DATABASE LINK                     NO
SYS                            DROP PUBLIC DATABASE LINK                NO
SYS                            CREATE PUBLIC DATABASE LINK              NO
 

可以看出，在資料庫中DBLINK有3種許可權：CREATE DATABASE LINK（所建立的DBLINK只能是建立者自己使用，別的使用者不能使用）、CREATE PUBLIC DATABASE LINK（PUBLIC表示所建立的DBLINK所有使用者都可以使用）與DROP PUBLIC DATABASE LINK（刪除公共的DBLINK）。可以使用如下授權語句給使用者SCOTT授予CREATE PUBLIC DATABASE LINK和DROP PUBLIC DATABASE LINK兩個許可權：

GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK TO SCOTT;

查詢資料庫的DBLINK可以使用SQL語句：SELECT * FROM DBA_DB_LINKS。

建立DBLINK一般有兩種方式，第一種方式是在本地資料庫tnsnames.ora檔案中配置了要遠端訪問的資料庫的TNS，例如這裡配置了TNS_BJLHR，然後建立公共DBLINK：

CREATE PUBLIC DATABASE LINK

DBL_BJLHR CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TNS_BJLHR';

其中，DBL_BJLHR是建立的DBLINK名字，SCOTT/TIGER是登入到遠端資料庫的使用者名/密碼，然後在本地資料庫中透過DBLINK訪問遠端資料庫“TNS_BJLHR”中SCOTT.TB_TEST表，SQL語句如下所示：

SELECT * FROM SCOTT.TB_TEST@DBL_BJLHR;

建立DBLINK的第二種方式，是在本地資料庫tnsnames.ora檔案中沒有配置要訪問的遠端資料庫的時候，而直接將相關的內容寫到DBLINK的配置中，如下所示：

CREATE DATABASE LINK DBL_BJLHR

CONNECT TO SCOTT IDENTIFIED BY TIGER

USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BJLHR)

)

)';

其中，HOST為資料庫的IP地址，SERVICE_NAME為資料庫的SID，其實，兩種方法配置DBLINK是差不多的，第二種方法不受本地服務的影響。

另外，當資料庫引數GLOBAL_NAMES為TRUE時，要求資料庫連結名稱跟遠端資料庫名稱一樣。資料庫全域性名稱可以用以下命令查出：

SELECT * FROM GLOBAL_NAME;

用以下語句來修改引數值：

ALTER SYSTEM SET GLOBAL_NAMES=FALSE/TRUE;

當資料庫引數GLOBAL_NAMES為FALSE時，就不要求資料庫連結名稱跟遠端資料庫名稱一樣。

 

11.4.2 一道OCM考題
1. Create DBLINK
1.1 Create DBLINK, should query on the PROD2 database all the table under the system user in the PROD1 Database.


1. Create Synonym
 1.1 Create a sh.sales@PROD1 on the public synonyms in the PROD2, requires that all users can access.


題目要求在PROD2資料庫上可以查詢PROD1資料庫上system使用者下的所有表，並且在prod2上建立一個sh.sales@PROD1的同義詞：
create public database link PROD1 connect to system identified by oracle using 'PROD1';
create public synonym sy_ss for sh.sales@PROD1;

---

---

   1、概念：database link是定義一個資料庫到另一個資料庫的路徑的物件，database link            

      允許你查詢遠端表及執行遠端程式。

   

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

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

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

   

   3、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.

  4、建立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.

  

---

---

1、如果需要建立全域性 DBLink，則需要先確定使用者有建立 dblink 的許可權：

如果沒有，則需要使用 sysdba 角色給使用者賦權：

2、使用該使用者登入 PL/SQL，使用命令：

採用圖形配置介面則如下所示：

3、查詢資料：

4、刪除 DBLink

5、建立和刪除同義詞

6、建立和刪除檢視

7、注意：

建立 DBLink 很簡單，但是在使用中後臺卻出現鎖，檢視這個鎖的方法可以去 console 中看到或者查詢資料庫。每次使用dblink查詢的時候，均會與遠端資料庫建立一個連線，dblink 應該不會自動釋放這個連線，如果是大量使用 dblink 查詢，會造成 web 專案的連線數不夠,導致系統無法正常執行，導致系統無正常執行。

---

---

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 ;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John
透過執行select * from ; 進行測試，結果OK

 

二）對於GLOBAL_NAMES = TRUE的情況，資料庫連結（DATABASE LINK）的名字必須和資料庫的名字相同：
在本地伺服器上執行下面語句使GLOBAL_NAMES=TRUE：
SQL>ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
再查詢時，會有如下的錯誤：
SQL> select * from ;
select * from 
                         *
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

;”的時候，就會出現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 ;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John

SQL> UPDATE  t set t.name='WatsonModified' where id=1;

1 row updated.

SQL> select * from ;

        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.

---

---

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 StatementConnects To DatabaseConnects AsLink 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> exec dbms_utility.EXEC_DDL_STATEMENT@dblink('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

 

---

---

ORA-02024: database link not found 

 刪除database link 時發現報錯 ： ORA-02024: database link not found  




1. 確認database link是否public database link ,如果是，不必是這個dblink的owner, 有
足夠的許可權即可刪除 。


2. 如果是private database link，那麼需要owner來刪除，否則會報錯。


3. 建立前後更改了global_name ,原來global_name=true, 建立dblink後被更改為了false . 
這種情況下即使再更改回來也不能正常刪除dblink。詳見： 
Cannot drop a database link after changing the global_name ORA-02024 (文件 ID 382994.1) 

---

---

刪除 Dblink 報錯 ORA-02024: database link not found 的解決方法

朋友說刪除DBLINK 報錯： ORA-02024: database link not found。

 

在Metalink上搜到了2篇與這個錯誤有關的文章：

       Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]

       http://blog.csdn.net/tianlesoftware/archive/2011/01/23/6160082.aspx

 

       ORA-02024: Database Link Not Found [ID 1058949.1]

       http://blog.csdn.net/tianlesoftware/archive/2011/01/23/6160080.aspx

 

兩篇文章分別講到了2種情況。

 

一 .  DBLINK所有者不一致造成

我們來演示一下這種情況。

 

--建立dblink

SQL> conn system/admin;

已連線。

SQL>  create database link dave connect to system identified by admin using '

  2   (DESCRIPTION =

  3   (ADDRESS_LIST =

  4   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.100)(PORT = 1521))

  5   )

  6   (CONNECT_DATA =

  7   (SERVICE_NAME = newccs)

  8   )

  9   )

 10   ';

 

資料庫連結已建立。

 

DBLINK 建立這塊可以參考官網說明：

CREATE DATABASE LINK

 

--檢視驗證dblink

SQL> select name from v$database@dave;

 

NAME

---------

NEWCCS

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

 

SQL> select * from all_db_links;

OWNER      DB_LINK    USERNAME   HOST       CREATED

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

SYSTEM     DAVE       SYSTEM                2011-01-23 21:17:31

 

--我們用錯誤的所有者來刪除看看

SQL> drop public database link dave;

drop public database link dave

                          *

第 1 行出現錯誤:

ORA-02024: 未找到資料庫連結

 

--用正確的使用者來刪除

SQL> drop database link dave;

資料庫連結已刪除。

 

 

二. Global_name 造成

 

先看官網的一段說明：

       If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.

       The maximum number of database links that can be open in one session or one instance of an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

             

       Global_names 是一個布林值，global_names的作用是建立db link時是否強制使用遠端資料庫的global_name,如果global_names=true,則db link name必須要求是remote database的global_name,否則建立之後db link 不能連同，預設值是false。 當global_name 為False時，如果我們修改了global_name，那麼在dblink中也要相應的指定global_name. 並且當我們修改了global_name後，之前存在的我們dblink也無法刪除。

       Global_name 是由db_name.db_domain構成。

 

有關這幾個引數的說明，參考：

       DBID,SID,DB_NAME,DB_DOMAIN,INSTANCE_NAME,DB_UNIQUE_NAME,SERVICE_NAMES 及監聽引數的說明

http://blog.csdn.net/tianlesoftware/archive/2010/12/20/6086066.aspx

 

CREATE DATABASE LINK

 

open_links_per_instance 和 open_links 引數說明

 

下面來看示例：

SQL> show parameter global_names

NAME             TYPE        VALUE

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

global_names        boolean     FALSE

 

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS

 

SQL> create database link dave connect to system identified by admin using 'newccs';

資料庫連結已建立。

 

SQL> select * from dba_db_links;

OWNER      DB_LINK    USERNAME   HOST       CREATED

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

SYSTEM     DAVE       SYSTEM     newccs     2011-01-23 21:45:41

 

SQL>  select name from v$database@dave;

NAME

---------

NEWCCS

 

       這裡要注意一點，我這裡建立的dblink 使用的是監聽名：newccs. dba_db_links表中的host顯示的是這個監聽的名字。 在第一個示例中，我建立dblink 是使用命令來寫的，那種情況下host 為空。

 

       在上面提到，當global_names為False的情況下，如果我們修改了global_name，之前建立的dblink 在刪除的時候也會包ORA-02024的錯誤。 甚至我們把global_name 改變成原來的值，也無法刪除。 因為這種改變沒有生效。 除非我們更新props$ 表。

 

 

 

先來驗證global_name 修改後生效問題。

 

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS

 

SQL> alter database rename global_name to newccs.tianlesoftware.com;

資料庫已更改。

 

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS.TIANLESOFTWARE.COM

 

SQL> alter database rename global_name to newccs;

資料庫已更改。

 

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS.TIANLESOFTWARE.COM

--這裡並沒有生效。

 

SQL> drop database link dave;

drop database link dave

                   *

第 1 行出現錯誤:

ORA-02024: 未找到資料庫連結

 

 

       從上面的操作，驗證瞭如果僅透過alter 命令，是無法讓global_name 還原成原來的值的。並且當global_name 發生改變後，已經存在的dblink也無法刪除。

 

       解決這個問題的方法，就是用sys使用者來修改props$表：

       SQL> update props$ set value$ = '' where name ='GLOBAL_DB_NAME';

 

 

示例：

SQL> conn / as sysdba;

已連線。

SQL> update props$ set value$ = 'NEWCCS' where name ='GLOBAL_DB_NAME';

 

已更新 1 行。

SQL> commit;

提交完成。

 

然後切換到dblink 的使用者，刪除dblink：

SQL> conn system/admin;

已連線。

SQL> drop database link dave;

資料庫連結已刪除。

 

如果還報這種錯誤，可以重新整理三次shared pool， 在執行drop命令：

alter system flush SHARED_POOL;

alter system flush SHARED_POOL;

alter system flush SHARED_POOL;

 

如果重新整理shared pool後還無法drop，那麼就需要重啟DB，在刪除了。

 

---

---

Cannot drop a database link after changing the global_name ORA-02024 (Doc ID 382994.1)

In this Document

Symptoms

Changes

Cause

Solution

References

---

APPLIES TO:

There are two solutions:

        1.3.3. If step b doesn't help, you need to bounce your database and try to drop the database link.

        1.3.4. Once the database link is dropped, the global_name can be changed back to the desired name containing domain part using the alter database rename global_name statement

2. The second solution consists on deleting the database link directly from sys.link$:

Reference

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2018-01-01 06:00 ~ 2018-01-31 24:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ● 好訊息：小麥苗OCP、OCM開班啦，詳情請點選：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................