oracle opaque_transform,使用dblink导致的/*+ OPAQUE_TRANSFORM */

数据库版本

--目标端

SQL> select * from v$version;

BANNER

-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

--源端

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

目标端创建dblink

SQL> create database link dblink_xff connect to test identified by

2 test using 'ip/mcrm';

数据库链接已创建。

dblink查询操作测试

--目标端

SQL> select count(*) from [email protected]_xff;

COUNT(*)

----------

50645

--源端

SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'

and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT

-------------------------------------------------------------------

SELECT COUNT(*) FROM "T_XIFENFEI" "A1"

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建空表测试

--目标端

SQL> create table chf.t_xifenfei as select * from [email protected]_xff where 1=0;

表已创建。

--源端

SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'

and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT

----------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建表插入数据

--目标端

SQL> create table chf.t_xifenfei_new as select * from [email protected]_xff;

表已创建。

--源端

SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'

and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT

--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",

"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

dblink insert select插入数据测试

--目标端

SQL> insert into chf.t_xifenfei

2 select * from [email protected]_xff;

已创建 50645 行。

--源端

SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'

and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT

--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID

","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS",

"TEMPORARY","GENERATED","SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

除掉OPAQUE_TRANSFORM 提示

--目标端

SQL> alter session set events '22825 trace name context forever, level 1' ;

会话已更改。

SQL> insert into chf.t_xifenfei

2 select * from [email protected]_xff;

已创建 50645 行。

--源端

SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' and sq

l_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT

--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",

"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

通过dblink的相关实验可以得出,在 insert-as-remote-select的时候,源端库上会出现/*+ OPAQUE_TRANSFORM */的hint提示.该hint的作用是:给出源端目标端要求的数据类型的明确信息(The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database).屏蔽盖hint的方法是设置event:22825 trace name context forever, level 1(官方文档还提供了另外两种hint的方式屏蔽这个,但是我测试均未成功)