[20180211]current_schema与dblink.txt

--//有时候调优sql语句,经常在回话设在alter session set current_schema=scott,然后执行sql语句.
--//如果使用dblink是private,存在问题,做一个记录:

1.环境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE PUBLIC DATABASE LINK LOOPBACK
CONNECT TO SCOTT
IDENTIFIED BY <PWD>
USING 'localhost:1521/book:DEDICATED';

CREATE DATABASE LINK LOOP
CONNECT TO SCOTT
IDENTIFIED BY <PWD>
USING 'localhost:1521/book:DEDICATED';

2.测试:
SYS@book> show user;
USER is "SYS"

SYS@book> alter session set current_schema=scott;
Session altered.

SYS@book> select * from dept@loopback ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SYS@book> select * from dept@loop ;
select * from dept@loop
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

$ oerr ora 2019
02019, 00000, "connection description for remote database not found"
// *Cause:
// *Action:

--//使用alter session时，私有的DBLINK依然不能使用，必须使用public DBLINK.如何使用呢?