这篇测试一下通过DBLink 访问含有Blob字段表的方法。 关于DBLINK 和 Lob 的说明，参考如下链接：

Oracle DBLink

http://blog.csdn.net/tianlesoftware/article/details/4698642

删除Dblink 报错ORA-02024: database link not found 的解决方法

http://blog.csdn.net/tianlesoftware/article/details/6160196

ORACLE LOB 大对象处理

http://blog.csdn.net/tianlesoftware/article/details/5070981

Oracle LOB 详解

http://blog.csdn.net/tianlesoftware/article/details/6905406

一．模拟问题

1.1  在实例1上操作：

创建含有blob 的测试表：

/* Formatted on 2012/6/19 10:18:05 (QP5 v5.185.11230.41888) */ CREATE TABLE lob1 ( line NUMBER primary key, text CLOB ); INSERT INTO lob1 SELECT distinct line, text FROM all_source where rownum<500; SELECT segment_name, segment_type, tablespace_name, SUM (bytes) / 1024 / 1024 || 'M' AS "SIZE" FROM user_segments WHERE segment_name = 'LOB1' GROUP BY segment_name, segment_type, tablespace_name;

LOB 表的信息如下：

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE --------------- ------------------ ------------------------------ ---------- LOB1 TABLE SYSTEM 9M SQL> set wrap off; SQL> select * from lob1 where rownum=1; LINE TEXT ---------- --------------------------------- 1 package STANDARD AUTHID CURRENT_USER is -- care
 

1.2 在实例2上操作

创建DBLINK：

CREATE PUBLIC DATABASE LINK lob_link CONNECT TO dave IDENTIFIED BY dave USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.222)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dave) ) )'; SQL> select * from v$version; BANNER ---------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> select count(*) from lob1@lob_link; COUNT(*) ---------- 58228
 

这边查询总记录数正常。但是当我们查询具体LOB字段里的内容的时候，就会报错，如下：

SQL> select * from lob1@lob_link where rownum=1; ERROR: ORA-22992: cannot use LOB locators selected from remote tables no rows selected [oracle@localhost ~]$ oerr ora 22992 22992, 00000, "cannot use LOB locators selected from remote tables" // *Cause: A remote LOB column cannot be referenced. // *Action: Remove references to LOBs in remote tables.
 

二．MOS 上的相关说明

2.1 ORA-22992 When TryingTo Select Lob Columns Over A Database Link [ID 119897.1]

       在官网的这篇文章中，基于Oracle 8i的进行了说明，报错的原因是因为不支持这个特性。

 (A)You cannot actually select a lob column (i.e. CLOB column) from a table

    using remote database link.  Thisis not a supported feature.

 (B)Also, these are the INVALID operations on a LOB column:

       --以下操作也不被支持。

&nb