数据库版本
--目标端 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 t_xifenfei@dblink_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 t_xifenfei@dblink_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 t_xifenfei@dblink_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 t_xifenfei@dblink_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 t_xifenfei@dblink_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的方式屏蔽这个,但是我测试均未成功)
