使用DBLink過程中遇到的問題有哪些

這篇文章給大家分享的是有關使用DBLink過程中遇到的問題有哪些的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

1.       創建DBLink

1.1創建命令

create database link zlbak connect to zlbak01 identified by his using 'orcl';

注意，引號里的orcl，是在數據庫服務器配置好的服務名，不是客戶端本機配置的。

1.2名稱

如果參數global_names為True，則要求創建的DBLink名稱必須與被連接庫的global_name相同。

create database link orcl connect to zlbak01 identified by his using 'orcl';

被連接的庫，global_name可能很長，例如：

select * from global_name;

ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

可以通過下面的命令將名稱改短，去掉點后面的字符。

update global_name set global_name = 'orcl';

注意，千萬不能改為空，否則會造成數據庫無法啟動，需要用特殊的方法才能解決。

如果通過下面這種方法修改，之前有域名的話，修改后仍然會有點后名的域名。

alter database rename global_name to orcl

當global_names為True時，如果要建多個DBLink指向同一個庫，不能重名，怎么辦呢？

create database link orcl@link1 connect to zlbak01 identified by his using 'orcl';

create database link orcl@link2 connect to zlbak01 identified by his using 'orcl';

原來，需要在GLOBAL_NAME后面加上@再加上一個標識。

既然global_names為True時這么麻煩，是否可以改為false呢？

如果不用流復制的話，完全是可以的，修改方法：

alter system set global_name=false;

修改后重新啟動數據庫設置才能生效，改成false之后，DBLink的名稱就可以隨意取了。

1.3特殊案例

記得是在2014年，做第一家用戶的歷史數據轉出，完成之后，準備通過DBLink來實現遠程歷史庫的查詢，遇到一個奇怪的問題。

通過下面的命令創建的DBLink：

create database link zlbak connect to zlbak01 identified by his using 'orcl';

注意，引號里的orcl，是在數據庫服務器配置好的服務名，不是客戶端本機配置的。

創建好之后，無法正常使用，執行查詢報錯：

select * from 人員表@zlbak

ORA-12543：TNS：無法連接目標主機。

在服務器上，用tnsping服務名orcl是通的。

在sqlplus中通過zlbak01用戶連接orcl也可以正常登錄。

用戶環境是10.2.0.5 windows 64bit。

弄了一個晚上，最后改成下面這種方式才成功了。

create database link zlbak connect to zlbak01 identified by his using
'(DESCRIPTION = (ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)

(HOST=192.1.68.1.1)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=orcl)))';

2.       通過DBLink查詢遠程數據

2.1不支持查詢含有LOB類型字段的遠程表

當遠程表含有blob,clob,xmltype,long等大數據類型字段時，通過DBLink查詢會報錯：

ORA-22992: 無法使用從遠程表選擇的 LOB 定位器

目前已知的兩種做法：

a.       對象表加函數轉換

源端

1)      創建OBJECT類型，字段跟原表相同，只是把LOB字段改為Varchar2(4000)

2)      以該類型再創建一個Table類型

3)      創建一個讀取轉換函數，將原表的數據，插入到Table類型中并返回

以游標循環方式，用dbms_lob.getlength和dbms_lob.substr，每次最多取4000個字符，用Pipe Row管道函數輸出，需要用自治事務，函數返回前提交事務。

4)      創建一個視圖，調用該函數，并以Table語句轉換為二維表。

目標端

1）  定義與源端相同的OBJECT類型和Table類型

2）  創建一個接收轉換函數，把從源端接收到的Varchar2(4000)拼接后轉換為LOB類型。

需要用到自治事務

3）  創建一個視圖，通過DBLink查詢數據，通過上一步創建的函數轉換為LOB字段。

4）  查詢這個視圖，就像查詢表的數據一樣，可以正常返回LOB字段數據。

這種方法雖然能夠實現，但是由于視圖加函數方式，無法利用索引，所以，只能做為臨時性的查詢手段，并且實現起來比較麻煩。

b.       臨時表

雖然不支持直接通過DBLink方式查詢LOB類型的字段，但是insert into ……Select方式是支持的。所以，可以在本地服務器創建一個臨時表，將查詢的遠程數據表的數據插入到臨時表，然后，通過臨時表就可以正常訪問到含有LOB類型字段的表了。

這種方式實現起來比“對象表加轉換函數”簡單得多，重要的是可以用到索引。

2.2通過driving_site來指定驅動表

很多技術人員在用戶環境中，通過DBLink來關聯查詢一些業務系統的遠程數據表，但是，可能大多數人并沒有注意到一個問題：遠程數據表的全表復制。

你可能以為它會像本地表一樣用到索引，實際上，很可能根本就沒有用到索引，不信你可以馬上看看那些SQL的執行計劃，注意分析其中操作為Remote的行，那就是全表復制。

訪問遠程數據表時，如果在索引相關字段的查詢條件中直接指定了值，則可以直接利用索引。

例如：

Select id from H病人醫囑記錄 where 病人id=:v1;

其中” H病人醫囑記錄”是一個通過DBLink連接到遠程數據庫表的視圖。

但是，以下幾種常見情況，是沒有利用遠程表的索引的：

1）  本地表和遠程表的連接

2）  Table內存表和遠程表的連接

3）  索引相關的查詢條件用到了Or

例如：

Select b.Id, b.類別, c.名稱 As 類別名稱, b.名稱, b.標本部位

From H病人醫囑記錄 A, 診療項目目錄 B, 診療項目類別 C,

Table(f_Num2list('43190722, 43190723')) D

Where a.Id = d.Column_Value And a.診療項目id = b.Id And b.類別 = c.編碼

這種情況，會將遠程數據表的全部數據查詢后傳輸到本地服務器，再進行表間連接。

當遠程表是大表，本地表是小表，關聯查詢時，需要決定數據復制的方向，這是分布式數據訪問都存在的一個問題。

在Select后加提示字/*+driving_site(a)*/這種方式可以指定遠程表為驅動表，把本地的小表復制到遠程，這樣就可以用到索引了，并且避免了復制大表數據到本地服務器。

但是，如果含有Table內存表這種情況，還是無法利用索引，因為內存表的數據不支持作為被驅動表復制到遠程，可以改寫查詢，避免使用Table方式。例如，用in方式，直接將條件值傳入，帶來的問題是無法使用綁定變量，對于歷史數據查詢這種低頻業務，不使用綁定變量是可以接受的。

注意，driving_site對dml無效(insert,delete,update)，dml以目標表所在庫驅動SQL計劃。

還有下面這種情況，雖然都是遠程表連接，但是因為使用了Or，導致執行計劃沒有使用索引，可以調整為將Or展開，寫成Union All方式。

Select Distinct b.發送號, b.發送人 As 人員, b.發送時間 As 時間

From H病人醫囑記錄 A, H病人醫囑發送 B

Where a.Id = b.醫囑id And (a.Id = 43895356 Or a.相關id = 43895356)

Order By 時間 Desc, 發送號

2.4查詢DBLink后需要關閉連接

通過db-link執行查詢后，當前session到遠程數據庫的連接是不會自動關閉的，在基于連接池的管理中可能會引起目標數據庫的Session泛濫，從而消耗進程資源。

這種情況下，可以在查詢完成之后執行關閉連接命令：

alter sesssion close database link orcl;

注意需要先執行commit命令。也可以使用系統包來關閉連接：

DBMS_SESSION.CLOSE_DATABASE_LINK(orcl);

2.5提交事務

在使用PL/SQL developer里面通過dblink執行查詢后，

commit和rollback會亮，是什么原因呢？

下面的測試可以發現一些規律：

select count(1) from test@dblink;

不會產生commit提示

select * from test@dblink;

會產生commit提示

select * from test@dblink where rownum<5;

不會產生commit提示

原來，當需要的數據都返回了，就不會產生commit提示，否則就會產生commit提示。

通過下面的語句，可以查到回滾段的情況：

Select s.Sid, s.Serial#, s.Sql_Hash_Value,

r.Segment_Name, t.Xidusn, t.Xidslot, t.Xidsqn

From V$session S, V$transaction T, Dba_Rollback_Segs R

Where s.Taddr = t.Addr And t.Xidusn = r.Segment_Id(+);

3.其他

3.1以下兩個參數可以調整打開的DBLink數量。

open_links :每個session最多允許的dblink數量;

open_links_per_instance:指每個實例最多允許的dblink個數

當前打開的DBLink可以查詢視圖v$dblink。

3.2通過DBLink插入數據到遠程數據庫

含有XMLType等對象類型或用戶定義類型字段的表，不支持將通過DBLink插入到遠程數據庫。

所以，直接將要轉移的歷史數據通過DBLink插入到遠程歷史庫，有些表是不支持的。

3.3不支持通過DBLink執行DDL語句

         這個比較可以理解，必竟修改數據結構，涉及到的關聯處理太多，例如并發控制等。

感謝各位的閱讀！關于“使用DBLink過程中遇到的問題有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！