Oracle DATABASE LINK(DBLINK)創(chuàng)建與刪除方法

Oracle創(chuàng)建、刪除DATABASE LINK

創(chuàng)建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]; 

今天在試著刪除的時(shí)候報(bào)錯(cuò)：
ORA-02018：database link of same name has an open connection

關(guān)閉dblink：

ALTER SESSION CLOSE DATABASE LINK [name];

但是報(bào)錯(cuò)：
ORA-02080: database link is in use
去v$dblink查看

SELECT * FROM v$dblink;

 
該dblink確實(shí)沒有在transaction，很疑惑。搜一下沒找到解決辦法，然后把plsql關(guān)了，過一會(huì)再開，執(zhí)行ALTER SESSION的時(shí)候，報(bào)錯(cuò)：
ORA-02080: database link is not open
我知道這時(shí)應(yīng)該可以刪除了， 然后再DROP，可以了。

下面是其它網(wǎng)友的補(bǔ)充大家可以參考下：

數(shù)據(jù)庫(kù)全局名稱可以用以下命令查出：

ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;

1>、當(dāng)數(shù)據(jù)庫(kù)參數(shù)global_name=false時(shí)，就不要求數(shù)據(jù)庫(kù)鏈接名稱跟遠(yuǎn)端數(shù)據(jù)庫(kù)名稱一樣。

Oracle數(shù)據(jù)庫(kù)之間進(jìn)行連接通訊。
創(chuàng)建數(shù)據(jù)庫(kù)鏈接的語法如下：

CREATE [PUBLIC] DATABASE LINK link
CONNECT TO username IDENTIFIED BY password

USING ‘connectstring'

注意：創(chuàng)建數(shù)據(jù)庫(kù)鏈接的帳號(hào)必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統(tǒng)權(quán)限，用來登錄到遠(yuǎn)程數(shù)據(jù)庫(kù)的帳號(hào)必須有CREATE SESSION權(quán)限。這兩種權(quán)限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK權(quán)限在DBA中）。

一個(gè)公用數(shù)據(jù)庫(kù)鏈接對(duì)于數(shù)據(jù)庫(kù)中的所有用戶都是可用的，而一個(gè)私有鏈接僅對(duì)創(chuàng)建它的用戶可用。由一個(gè)用戶給另外一個(gè)用戶授權(quán)私有數(shù)據(jù)庫(kù)鏈接是不可能的，一個(gè)數(shù)據(jù)庫(kù)鏈接要么是公用的，要么是私有的。

創(chuàng)建數(shù)據(jù)庫(kù)鏈接時(shí)，還可以使用缺省登錄方式，即不指定遠(yuǎn)程數(shù)據(jù)庫(kù)的用戶名和密碼：

在不指定用戶名和口令的情況下，ORACLE使用當(dāng)前的用戶名和口令登錄到遠(yuǎn)程數(shù)據(jù)庫(kù)。

USING后面指定的是鏈接字符串，也就是遠(yuǎn)程數(shù)據(jù)庫(kù)的網(wǎng)絡(luò)服務(wù)名，這個(gè)服務(wù)名保存在本地TNSNAMES.ORA文件中，在該文件中定義了協(xié)議、主機(jī)名、端口和數(shù)據(jù)庫(kù)名。

刪除數(shù)據(jù)庫(kù)鏈接的語句是：

一般情況下引用數(shù)據(jù)庫(kù)鏈接，可以直接將其放到調(diào)用的表名或視圖名稱后面，中間使用一個(gè) @ 作為分割符：

SELECT * FROM worker@zrhs_link;

對(duì)于經(jīng)常使用的數(shù)據(jù)庫(kù)鏈接，可以建立一個(gè)本地的同義詞，方便使用：

CREATE SYNONYM worker_syn FOR worker@zrhs_link;

還可以建立一個(gè)本地的遠(yuǎn)程視圖，方便使用：

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

現(xiàn)在本視圖可與本地?cái)?shù)據(jù)庫(kù)中的任何其它視圖一樣對(duì)待，也可以授權(quán)給其它用戶，訪問此視圖，但該用戶必須有訪問數(shù)據(jù)庫(kù)鏈接的權(quán)限。

對(duì)于另外一種情況，所要訪問的表不在數(shù)據(jù)庫(kù)鏈接中指定的遠(yuǎn)程帳戶下，但該帳戶有訪問該表的權(quán)限，那么我們?cè)诒砻耙由显摫淼挠脩裘?/p>

SELECT * FROM camel.worker@zrhs_link ;

2>、當(dāng)數(shù)據(jù)庫(kù)參數(shù)global_name=true時(shí)，那就要求數(shù)據(jù)庫(kù)鏈接名稱跟遠(yuǎn)端數(shù)據(jù)庫(kù)全局名稱一樣

數(shù)據(jù)庫(kù)全局名稱可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

創(chuàng)建dblink的語法：

sql>create database link 數(shù)據(jù)庫(kù)鏈路名
connect to 用戶名 identified by 口令
using '主機(jī)字符串名';

如：

sql>create database link ora9i.us.oracle.com ### 這里的us.oracle.com為oracle默認(rèn)域名 ###
connect to scott identified by tiger
using 'sun';

1)dblink名必須與遠(yuǎn)程數(shù)據(jù)庫(kù)的全局?jǐn)?shù)據(jù)庫(kù)名（global_name）相同；
2)用戶名，口令為遠(yuǎn)程數(shù)據(jù)庫(kù)用戶名，口令;
3)主機(jī)字符串為本機(jī)tnsnames.ora中定義的串;
4)兩個(gè)同名的數(shù)據(jù)庫(kù)間不得建立dblink;
然后，你就可以通過dblink訪問遠(yuǎn)程數(shù)據(jù)庫(kù)了。

如：

sql>select * from 表名@ora9i.us.oracle.com;
還可以建立快照(snapshot)通過dblink實(shí)現(xiàn)遠(yuǎn)程數(shù)據(jù)自動(dòng)傳輸。
查看所有的數(shù)據(jù)庫(kù)鏈接，進(jìn)入系統(tǒng)管理員SQL>操作符下，運(yùn)行命令：

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

下面對(duì)第二種情況進(jìn)行舉例說明：

北京的總部有一個(gè)集中的數(shù)據(jù)庫(kù)，其SID是SIDBJ，用戶名：userbj,密碼：bj123,北京的IP地址是：192.168.1.101。
在本地(新疆)的分公司也有一個(gè)數(shù)據(jù)庫(kù)，其SID是SIDXJ，用戶：userxj，密碼：xj123，新疆的IP地址是：192.168.1.100。
要將本地新疆的SIDXJ數(shù)據(jù)庫(kù)中訪問到北京的數(shù)據(jù)庫(kù)SIDBJ中的數(shù)據(jù)。
也就是說，在sidxj的數(shù)據(jù)庫(kù)中，用戶userxj(192.168.1.100)需要建立DBLINK，以u(píng)serbj的用戶身份訪問sidBJ(192.168.1.101)中的數(shù)據(jù)。
測(cè)試環(huán)境：兩個(gè)數(shù)據(jù)庫(kù)均建立在WINXP上，ORACLE的版本均為Oracle817
建立環(huán)境時(shí)，要注意關(guān)閉兩臺(tái)計(jì)算機(jī)上的windows的防火墻，否則，會(huì)出現(xiàn)能ping通，但Oracle連接不通的情況。
1、問：如何返回?cái)?shù)據(jù)庫(kù)的GLOBAL_NAME？
執(zhí)行SELECT * FROM GLOBAL_NAME;
北京的數(shù)據(jù)庫(kù)的GLOBAL_NAME為SIDBJ.US.ORACLE.COM
新疆的數(shù)據(jù)庫(kù)的GLOBAL_NAME為SIDXJ
2、問：如何查看Global_name參數(shù)是true還是False?
答：執(zhí)行：SQL> show parameter global_name;
執(zhí)行的結(jié)果如下：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
表示該參數(shù)是true.

也可以通過查看v$option視圖，如果其中Advanced replication為TRUE，則支持高級(jí)復(fù)制功能；否則不支持。
執(zhí)行語句為：select * from v$option;
3、問：如何檢查Oracle的版本是否支持同步功能？
答：執(zhí)行select * from v$option where PARAMETER='Advanced replication'語句，
如何返回值為True，那么就是支持，否則就是不支持。在兩個(gè)數(shù)據(jù)庫(kù)中都是檢查是否支持才行。
建立步驟：

1、在本地建立一個(gè)Oracle的客戶端連接tns_xj_to_bj，用于連接北京的數(shù)據(jù)庫(kù)。
2、新建一個(gè)連接tnsxj，用于連接本地的sidxj數(shù)據(jù)庫(kù)，以tnsxj/userxj/xj123登錄到PLSQL中。
下面開始創(chuàng)建連接到北京的遠(yuǎn)程數(shù)據(jù)連接DBLink。
create database link SIDBJ.US.ORACLE.COM connect to userbj identified by bj123 using 'tns_xj_to_bj';
其中：SIDBJ.US.ORACLE.COM是遠(yuǎn)程的數(shù)據(jù)庫(kù)的global_name,userbj是連接SIDBJ的用戶名，bj123是userbj的密碼，
tns_xj_to_bj是本地建立的連接到北京的數(shù)據(jù)庫(kù)的服務(wù)名。
3、測(cè)試連接是否成功：
select * from dual@SIDBJ.US.ORACLE.COM
如果返回結(jié)果如下則表示連接成功了。
DUMMY
-----
X
4、查詢已經(jīng)建立的遠(yuǎn)程連接名：
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、至此，在新疆的計(jì)算機(jī)上建立了一個(gè)DBLINK，用于連接到北京的數(shù)據(jù)庫(kù)上，
在北京的數(shù)據(jù)庫(kù)中，建立一個(gè)表用于測(cè)試。
create table USERBJ.BJ_TEST
(
STU_ID NUMBER,
STU_NAME VARCHAR2(100)
)
在其中增加一條記錄：
insert into BJ_TEST (STU_ID, STU_NAME)
values (1, '鐘德榮');
在新疆的數(shù)據(jù)庫(kù)中查詢北京的數(shù)據(jù)庫(kù)中表的信息：
select * from bj_test@SIDBJ.US.ORACLE.COM
查詢結(jié)果：
STU_ID STU_NAME
---------- --------------------------------------------------------------------------------
1 鐘德榮
表示查詢是正常的。
說明：該查詢是用
以上腳本全部通過測(cè)試。