[20150907]关于db link.txt

[20150907]关于db link.txt

--有时候学习的需要，要测试db link的sql语句，需要使用到db link，而实际的情况仅仅1台机器，1个数据库。

--即使你什么都不建立。也可以使用global_name作为db link。

SCOTT@test> select global_name c10 from global_name;
C10
----------
TEST.COM

--另外说明以下实际上访问的是sys.props$ 表。现在执行计划就可以知道。
SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  451hbxv8pc8p2, child number 0
-------------------------------------
select global_name c10 from global_name
Plan hash value: 415205717
-----------------------------------------------------------------------------
| Id  | Operation         | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| PROPS$ |      1 |    28 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1 / PROPS$@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='GLOBAL_DB_NAME')

SCOTT@test> column value$ format a20
SCOTT@test> select * from sys.props$ where name='GLOBAL_DB_NAME';
NAME                 VALUE$               COMMENT$
-------------------- -------------------- ---------------------
GLOBAL_DB_NAME       TEST.COM             Global database name

SCOTT@test> select * from dept@test ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
...

SCOTT@test> select * from dept@test.com ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
...

SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dzump5hmtucsa, child number 0
-------------------------------------
select * from dept@test.com
Plan hash value: 3383998547
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     4 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |      5 |   100 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1

--不过这个并不是真正的db link，可以发现显示执行计划是访问的是本地的表。没有出现remote关键字。
--并且本机是不能建立这样的db link指向外部的数据库。

SCOTT@test> create database link "test.com" connect to SCOTT identified by "btbtms" using '192.168.100.89:1521/TEST';
create database link "test.com" connect to SCOTT identified by "btbtms" using '192.168.100.89:1521/TEST'
                                *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

--实际上即使指向本机也不行：
SCOTT@test> create database link "test.com" connect to SCOTT identified by "btbtms" using '40';
create database link "test.com" connect to SCOTT identified by "btbtms" using '40'
                                *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

--但是我们可以使用另外的名字：
CREATE PUBLIC DATABASE LINK "LOOPBACK.COM" USING 'localhost:1521/test.com';

SCOTT@test> select * from dept@loopback.com where rownum<=1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  frb221p0yatnu, child number 0

select * from dept@loopback.com where rownum<=1

NOTE: cannot fetch plan for SQL_ID: frb221p0yatnu, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

--这样就对了。

SCOTT@test> select * from dept@LOOPBACK ,emp where dept.deptno=emp.deptno;

SCOTT@test> @dpcz '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  d6pqq0q2zp054, child number 0
-------------------------------------
select * from dept@LOOPBACK ,emp where dept.deptno=emp.deptno
Plan hash value: 2412741621
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     8 (100)|          |        |      |       |       |          |
|*  1 |  HASH JOIN         |      |     14 |   812 |     8  (13)| 00:00:01 |        |      |  1000K|  1000K|  748K (0)|
|   2 |   REMOTE           | DEPT |      5 |   100 |     4   (0)| 00:00:01 | LOOPB~ | R->S |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |        |      |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" (accessing 'LOOPBACK.COM' )

--建立这样的db link 方便以后的测试工作。另外注意以下小细节，使用db link 即使select，也会产生redo。大家可以测试最前面的情
--况，不会产生redo。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1793043/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1793043/