oracle databse link

db_link

Table of Contents

• 1. 创建db_link
• 2. 注意事项
• 3. OPAQUE_TRANSFORM

1 创建db_link

create [public] database link <db link name> connect to username identified by passwod/value encryped password using 'connect string or tnsname';

2 注意事项

db_link 连接数据库，会导致两个数据库SCN的同步。对于过低的SCN ，由于迅速增长，可能会导致数据库无法启动，或者scn 接近celing值。

3 OPAQUE_TRANSFORM

关于这个hint 是10.2.0.3 之后出现的，在insert into table select * from table@db_link 这一类型的语句， 在执行时，源端数据库中出现的。

在MOS doc ID 780503.1 中对此有说明。文档内容如下:

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3
***Checked for relevance on 02-MAR-2012***
Goal

What is OPAQUE_TRANSFORM usage :

The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain
type of operations are done within the database.  For example object types .
It is also used for a insert-as-remote-select operation on a remote database



Example : insert into emp (select * from emp@rep102b)   ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.50          0          0          0           0
Execute      1      0.00       0.51          0          1         44          12
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       1.01          0          1         44          12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
     12  REMOTE  EMP (cr=0 pr=0 pw=0 time=508808 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
     12   REMOTE OF 'EMP' (REMOTE) [REP102B]
             SELECT /*+ OPAQUE_TRANSFORM */ "EMPNO","ENAME","JOB","MGR",
               "HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "EMP"


Note :  This hint should not interfere with the query optimizer plan.

Solution

The below event can be set on the client (local) in order to turn the opaque_transform hint on and off..
- To switch on :

alter session set events '22825 trace name context off' ;

- To switch off :

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

2)  or using the following hint : /*+ NO_QUERY_TRANSFORMATION */
3)  using  RULE hint.



-Note that if local site is 11g and remote is  11g server, this opens 2 sessions
   on the remote database and OPAQUE_TRANSFORM hint gives DX LOCK deadlock.

- If  the local is 10g client and remote is 11g server, this opens 1 session on
   the remote and no DX deadlock.

Author: halberd.lee

Created: 2020-07-12 Sun 01:22

Validate