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的方式屏蔽這個,但是我測試均未成功)