java oracle12c blob_Oracle 12c 新特性 --- 对CLOB、BLOB和XMLType进行分布式操作

概念https://www.cndba.cn/leo1990/article/2111https://www.cndba.cn/leo1990/article/2111

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定位器变量(远程定位器)

https://www.cndba.cn/leo1990/article/2111https://www.cndba.cn/leo1990/article/2111

实验

https://www.cndba.cn/leo1990/article/2111https://www.cndba.cn/leo1990/article/2111

https://www.cndba.cn/leo1990/article/2111https://www.cndba.cn/leo1990/article/2111

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中完成

参考链接：

https://www.cndba.cn/leo1990/article/2111

https://www.cndba.cn/leo1990/article/2111

版权声明：本文为博主原创文章，未经博主允许不得转载。