概念
In this release, support for operations over database links for LOB-based data types, such as CLOB, BLOB and XMLType, is available.
This support enables operations on LOB-based data types across pluggable databases (PDBs) in an Oracle Multitenant environment.
在这个版本中，支持基于数据库链接的LOB-based数据类型的操作，比如CLOB、BLOB和XMLType。
这种支持支持在Oracle多租户环境中跨可插入数据库(PDBs)上的基于LOB-based数据类型的操作。
You can work with LOB data in remote tables is the following ways:
• Directly referencing LOB columns in remote tables (Remote LOB Columns) accessed using a database link.
• Selecting remote LOB columns into a local LOB locator variable (Remote locator)
您可以在远程表中使用LOB数据，方法如下:
•直接引用远程表中的LOB列(远程LOB列)，使用数据库链接访问。
•选择远程LOB列到本地LOB定位器变量(远程定位器)
实验
1) 在本机windows tnsname.ora 配置连接11g，12c 的服务
--Oracle 11g
cndba1.69 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cndba)
)
)
--Oracle 12c
pdb76 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdbcndba)
)
)
2）在本地服务器创建指向11g，12c 的DBlink
create public database link pdb_76
connect to test identified by test
using 'pdb76';
create public database link CNDBA69
connect to test identified by test
using 'cndba1.69';
3) 在11g,12c 数据库环境中创建表及CLOB 字段
[oracle@localhost ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 14 22:15:43 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t1 (x clob );
Table created.
SQL> insert into t1 values('yyy');
1 row created.
SQL> commit;
Commit complete.
[oracle@host1 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 14 22:55:10 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8793256 bytes
Variable Size 1023411032 bytes
Database Buffers 553648128 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> alter session set container=pdbcndba;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> conn test/test@pdbcndba
Connected.
SQL> create table t2(x clob );
Table created.
SQL> insert into t2 values('yyy');
1 row created.
SQL> commit;
Commit complete.
4） 查看数据库时，11g LOB 对象不支持分布式LOB操作，12C 可以
SQL> select * from t1@cndba69;
ERROR:
ORA-65510: 12.2 版本之前的数据库不支持分布式 LOB 操作。
未选定行
SQL> select * from t2@pdb_76;
X
--------------------------------------------------------------------------------
yyy
5） Create table as select or insert as select
只有独立的LOB列在选择列表中被允许以下列方式构造语句
SQL> CREATE TABLE t3 AS SELECT * FROM t2@pdb_76;
表已创建。
SQL> INSERT INTO t3 SELECT * FROM t2@pdb_76;
已创建 1 行。
SQL> UPDATE t3 SET x = (SELECT x FROM t2@pdb_76);
已更新 2 行。
SQL> INSERT INTO t2@pdb_76 SELECT * FROM t3;
已创建 2 行。
SQL> UPDATE t2@pdb_76 SET x ='zzz';
已更新 3 行。
SQL> DELETE FROM t2@pdb_76 where rownum<=1;
已删除 1 行。
6）Functions on remote LOBs returning scalars
具有LOB参数并返回标量数据类型的SQL和PL/SQL函数得到支持。不支持其他SQL函数和DBMS_LOB api使用远程LOB列。例如，支持以下语句:
SQL> CREATE TABLE tab2 AS SELECT LENGTH(x) len FROM t2@pdb_76;
表已创建。
但是，不支持下面的语句，因为DBMS_LOB。子串函数返回一个LOB:
SQL> CREATE TABLE tab AS SELECT DBMS_LOB.SUBSTR(x) len from t2@pdb_76;
CREATE TABLE tab AS SELECT DBMS_LOB.SUBSTR(x) len from t2@pdb_76
*
第 1 行出现错误:
ORA-22992: 无法使用从远程表选择的 LOB 定位符
7）您可以从远程表中选择一个持久的LOB定位器到本地变量，这可以在PL/SQL或OCI中完成
参考链接：（文档非常好）
http://docs.oracle.com/database/122/NEWFT/new-features.htm#GUID-8B121B03-481B-4596-9855-1FBF68532095
http://docs.oracle.com/database/122/ADLOB/distributed-LOBs.htm#ADLOB-GUID-7E450E86-3E4E-4714-A164-FD36B93722F6
