Oracle DATABASE LINK(DBLINK)建立與刪除方法_oracle

Oracle建立、刪除DATABASE LINK

建立dblink：

create database link [name]  connect to [username] IDENTIFIED BY [password]  using '(DESCRIPTION =  (ADDRESS_LIST =   (ADDRESS = (PROTOCOL = TCP)(HOST = [ip])(PORT = [port]))  )  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = xxx)  )  )';

例如：

create public database link dblink_name connect to SYSTEM using '192.168.1.73:1521/oracle';

刪除dblink：

DROP DATABASE LINK [name]; --或 DROP PUBLIC DATABASE LINK [name]; 

今天在試著刪除的時候報錯：
ORA-02018：database link of same name has an open connection

關閉dblink：

ALTER SESSION CLOSE DATABASE LINK [name];

但是報錯：
ORA-02080: database link is in use
去v$dblink查看

SELECT * FROM v$dblink;

 
該dblink確實沒有在transaction，很疑惑。搜一下沒找到解決辦法，然後把plsql關了，過一會再開，執行ALTER SESSION的時候，報錯：
ORA-02080: database link is not open
我知道這時應該可以刪除了， 然後再DROP，可以了。

下面是其它網友的補充大家可以參考下：

資料庫全域名稱可以用以下命令查出：

複製代碼 代碼如下:

SELECT * FROM GLOBAL_NAME;

修改可以用以下語句來修改參數值：

ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;

<1>、當資料庫參數global_name=false時，就不要求資料庫連結名稱跟遠端資料庫名稱一樣。

Oracle資料庫之間進行串連通訊。
建立資料庫連結的文法如下：

CREATE [PUBLIC] DATABASE LINK linkCONNECT TO username IDENTIFIED BY passwordUSING ‘connectstring'

注意：建立資料庫連結的帳號必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統許可權，用來登入到遠端資料庫的帳號必須有CREATE SESSION許可權。這兩種許可權都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK許可權在DBA中）。

一個公用資料庫連結對於資料庫中的所有使用者都是可用的，而一個私人連結僅對建立它的使用者可用。由一個使用者給另外一個使用者授權私人資料庫連結是不可能的，一個資料庫連結要麼是公用的，要麼是私人的。

建立資料庫連結時，還可以使用預設登入方式，即不指定遠端資料庫的使用者名稱和密碼：

複製代碼 代碼如下:

create public database link zrhs_link
using ‘zrhs';

在不指定使用者名稱和口令的情況下，ORACLE使用當前的使用者名稱和口令登入到遠端資料庫。

USING後面指定的是連結字串，也就是遠端資料庫的網路服務名，這個服務名儲存在本地TNSNAMES.ORA檔案中，在該檔案中定義了協議、主機名稱、連接埠和資料庫名。

刪除資料庫連結的語句是：

複製代碼 代碼如下:

DROP [PUBLIC] DATABASE LINK zrhs_link

資料庫連結的引用

一般情況下引用資料庫連結，可以直接將其放到調用的表名或視圖名稱後面，中間使用一個 @ 作為分割符：

SELECT * FROM worker@zrhs_link;

對於經常使用的資料庫連結，可以建立一個本地的同義字，方便使用：

CREATE SYNONYM worker_syn FOR worker@zrhs_link;

還可以建立一個本地的遠程視圖，方便使用：

CREATE VIEW worker AS SELECT * FROM worker@zrhs_link where… ;

現在本視圖可與本機資料庫中的任何其它視圖一樣對待，也可以授權給其它使用者，訪問此視圖，但該使用者必須有訪問資料庫連結的許可權。

對於另外一種情況，所要訪問的表不在資料庫連結中指定的遠程帳戶下，但該帳戶有訪問該表的許可權，那麼我們在表名前要加上該表的使用者名稱：

SELECT * FROM camel.worker@zrhs_link ;

<2>、當資料庫參數global_name=true時，那就要求資料庫連結名稱跟遠端資料庫全域名稱一樣

資料庫全域名稱可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

建立dblink的文法：

sql>create database link 資料庫鏈路名
connect to 使用者名稱 identified by 口令
using '主機字串名';

如：

sql>create database link ora9i.us.oracle.com ### 這裡的us.oracle.com為oracle預設網域名稱 ###connect to scott identified by tigerusing 'sun';

1)dblink名必須與遠端資料庫的全域資料庫名（global_name）相同；
2)使用者名稱，口令為遠端資料庫使用者名稱，口令;
3)主機字串為本機tnsnames.ora中定義的串;
4)兩個同名的資料庫間不得建立dblink;
然後，你就可以通過dblink訪問遠端資料庫了。

如：

sql>select * from 表名@ora9i.us.oracle.com;
還可以建立快照(snapshot)通過dblink實現遠端資料自動傳輸。
查看所有的資料庫連結，進入系統管理員SQL>操作符下，運行命令：

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

下面對第二種情況進行舉例說明：

北京的總部有一個集中的資料庫，其SID是SIDBJ，使用者名稱：userbj,密碼：bj123,北京的IP地址是：192.168.1.101。
在本地(新疆)的分公司也有一個資料庫，其SID是SIDXJ，使用者：userxj，密碼：xj123，新疆的IP地址是：192.168.1.100。
要將本地新疆的SIDXJ資料庫中訪問到北京的資料庫SIDBJ中的資料。
也就是說，在sidxj的資料庫中，使用者userxj(192.168.1.100)需要建立DBLINK，以userbj的使用者身份訪問sidBJ(192.168.1.101)中的資料。
測試環境：兩個資料庫均建立在WINXP上，ORACLE的版本均為Oracle817
建立環境時，要注意關閉兩台電腦上的windows的防火牆，否則，會出現能ping通，但Oracle串連不通的情況。
1、問：如何返回資料庫的GLOBAL_NAME？
執行SELECT * FROM GLOBAL_NAME;
北京的資料庫的GLOBAL_NAME為SIDBJ.US.ORACLE.COM
新疆的資料庫的GLOBAL_NAME為SIDXJ
2、問：如何查看Global_name參數是true還是False?
答：執行：SQL> show parameter global_name;
執行的結果如下：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
表示該參數是true.

也可以通過查看v$option視圖，如果其中Advanced replication為TRUE，則支援進階複製功能；否則不支援。
執行語句為：select * from v$option;
3、問：如何檢查Oracle的版本是否支援同步功能？
答：執行select * from v$option where PARAMETER='Advanced replication'語句，
如何傳回值為True，那麼就是支援，否則就是不支援。在兩個資料庫中都是檢查是否支援才行。
建立步驟：

1、在本地建立一個Oracle的用戶端串連tns_xj_to_bj，用於串連北京的資料庫。
2、建立一個串連tnsxj，用於串連本地的sidxj資料庫，以tnsxj/userxj/xj123登入到PLSQL中。
下面開始建立串連到北京的遠端資料串連DBLink。
create database link SIDBJ.US.ORACLE.COM connect to userbj identified by bj123 using 'tns_xj_to_bj';
其中：SIDBJ.US.ORACLE.COM是遠端資料庫的global_name,userbj是串連SIDBJ的使用者名稱，bj123是userbj的密碼，
tns_xj_to_bj是本地建立的串連到北京的資料庫的服務名。
3、測試連接是否成功：
select * from dual@SIDBJ.US.ORACLE.COM
如果返回結果如下則表示串連成功了。
DUMMY
-----
X
4、查詢已經建立的遠端連線名：
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM SIDBJ.US.ORACLE.COM
USERXJ DBLINK_XJ_TO_BJ
USERXJ SIDBJ
5、至此，在新疆的電腦上建立了一個DBLINK，用於串連到北京的資料庫上，
在北京的資料庫中，建立一個表用於測試。
create table USERBJ.BJ_TEST
(
STU_ID NUMBER,
STU_NAME VARCHAR2(100)
)
在其中增加一條記錄：
insert into BJ_TEST (STU_ID, STU_NAME)
values (1, '鐘德榮');
在新疆的資料庫中查詢北京的資料庫中表的資訊：
select * from bj_test@SIDBJ.US.ORACLE.COM
查詢結果：
STU_ID STU_NAME
---------- --------------------------------------------------------------------------------
1 鐘德榮
表示查詢是正常的。
說明：該查詢是用
以上指令碼全部通過測試。