[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。