sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle dblink 使用dblink导致的/*+ OPAQUE_TRANSFORM */

时间:2016-07-22 22:15   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 使用dblink导致的/*+ OPAQUE_TRANSFORM */

1.数据库版本

--目标端
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

2.目标端创建dblink

SQL> create database link dblink_xff connect to test identified by
  2  test using 'ip/mcrm';

数据库链接已创建。

3.dblink查询操作测试

--目标端
SQL> select count(*) from t_oracleplus@dblink_xff;

  COUNT(*)
----------
     50645

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_oracleplus%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
-------------------------------------------------------------------
SELECT COUNT(*) FROM "T_oracleplus" "A1"
SELECT /*+ FULL(P) +*/ * FROM "T_oracleplus" P

4.dblink创建空表测试

--目标端
SQL> create table  chf.t_oracleplus as select * from t_oracleplus@dblink_xff where 1=0;

表已创建。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_oracleplus%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
----------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_oracleplus" P

5.dblink创建表插入数据

--目标端
SQL> create table  chf.t_oracleplus_new as select * from t_oracleplus@dblink_xff;

表已创建。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_oracleplus%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_oracleplus" 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_oracleplus" "T_oracleplus"

6.dblink insert select插入数据测试

--目标端
SQL> insert into chf.t_oracleplus
  2  select * from t_oracleplus@dblink_xff;

已创建 50645 行。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_oracleplus%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_oracleplus" 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_oracleplus" "T_oracleplus"

7.除掉OPAQUE_TRANSFORM 提示

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

会话已更改。

SQL> insert into chf.t_oracleplus
  2  select * from t_oracleplus@dblink_xff;

已创建 50645 行。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_oracleplus%' and sq
l_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_oracleplus" 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_oracleplus" "T_oracleplus"

通过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的方式屏蔽这个,但是我测试均未成功)


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle dblink 使用dblink导致的/*+ OPAQUE_TRANSFORM */

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/830.html

Oracle研究中心

关键词:

Oracle dblink

使用dblink导致的/*+ OPAQUE_TRANSFORM */