作者：曾让我心碎的你俺_275 | 来源：互联网 | 2023-08-16 11:47
这篇测试一下通过DBLink 访问含有Blob字段表的方法。
一．模拟问题
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
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.
京公网安备 11010802041100号