今天業務需要，要跨庫查詢資料，於是想到了dblink, 下面一一細說，oracle dblink.

首先oracle dblink 分成如下三種：

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：

一：建立 public database link 的例項：

create public database link todb151

connect to hezi identified by manager

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.4.151)(PORT = 18000))

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';

應該注意的是：

1，上面的hezi 這個使用者，是你要連線的遠端資料庫中的使用者（也就是 4.151上的）。

2，SQL> select * from pd_zh_cn.tb_goods@todb151; 透過todb151來跨庫查詢4.151，會擁有hezi的查詢許可權。只要本地的使用者擁有資料庫訪問許可權即可，所以儘量不要建public資料庫連線，或者不要用大許可權使用者hezi來建立，以免讓本地小許可權的使用者，去遠端4.151檢視到了不該看到的資訊。

3.using 後面的引號中間的內容實際上就有本地tnsname.ora 中的相應資料庫連線資訊。所以這裡也可以直接改寫成：

create public database link todb151

connect to hezi identified by manager

using 'db151' ;

前提是本地tnsname.ora 檔案中有：

db151 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.151)(PORT = 18000))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = newzqdb)

)

其中SERVICE_NAME 一般是下面紅字部分，

SQL> show parameter service_names

NAME TYPE VALUE

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

service_names string newzqdb

還要注意要和監聽的服務名一致，因為這個遠端是要藉助監聽的。這裡明確的告訴大家，配置靜態監聽註冊時，需要輸入的全域性資料庫名(GLOBAL_DBNAME)輸入什麼都可以，只要保證listerner.ora中的GLOBAL_DBNAME和tnsnames.ora中的SERVICE_NAME保持一致，所以如果是靜態監聽，那麼在建立dblink時應該也應該符合，service_name =listerner.ora中的GLOBAL_DBNAME ，（建立dblink時不一定非得一樣）

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JUL-2015 21:39:53

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=newzq.com.cn)(PORT=18000)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                01-JUL-2015 20:18:41
Uptime                    0 days 1 hr. 21 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/products/database/11.2.0.1/network/admin/listener.ora
Listener Log File         /u01/oracle/products/diag/tnslsnr/newzq/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=newzq.com.cn)(PORT=18000)))
Services Summary...
Service "newzqdb" has 1 instance(s).
  Instance "newzqdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

二：建立 private dblink 例項：注意沒有private字眼。

create database link dblink

connect to liuwenhe identified by liuwenhe

using '(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';

管理dblink:

在當前資料庫下檢視所有的DBLINK的方法：

1. select * from dba_db_links;

刪除當前資料庫下的一個指定的DBLINK的方法：

1. 刪除public link

drop public database link dblink;

2. 刪除private link

drop database link dblink;

ORA-02085 解決辦法：

// *Cause: a database link connected to a database with a different name.
// The connection is rejected.
// *Action: create a database link with the same name as the database it
// connects to, or set global_names=false.
//

2）從報錯資訊的描述上已經可以得到問題的原因和處理方法
錯誤原因：使用的dblink名字與要連線的資料庫名字不相同
處理方法：有兩種處理方法
第一種處理方法：建立與對方資料庫名字相同的dblink
第二種處理方法：調整資料庫引數global_names值為false，取消這種限制

一定要理解具體怎麼操作

global_names和global_name看起來很相似，global_names的作用是建立db link時是否強制使用遠端資料庫的global_name,如果

global_names=true,則db link name必須要求是remote database的global_name,否則建立之後db link 不能連同，

假如在本地：不是遠端，和自己的思維不一樣。

SQL> show parameter global_names

NAME TYPE VALUE

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

global_names boolean TRUE

這樣在本地要想建立dblink ,那麼該dblink的名字，必須是遠端4.151的global_name ,如下檢視global_name

SQL> select * from global_name;

GLOBAL_NAME

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

NEWZQDB

也就是隻能起名為newzqdb ，都則報錯ORA-02085

create public database link newzqdb

connect to system identified by manager

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.4.151)(PORT = 18000))

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';

你可以在本地透過 alter system set global_names=FALSE; 來取消這種限制，一定注意在本地，不是遠端。

也就是說如果本地global_names這個引數是TRUE，那麼在這裡只能建立一個可以使用的dblink。

oracle DBlink建立和維護以及ORA-02085 解決辦法

相關文章