[20180211]current_schema与dblink.txt

[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

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.如何使用呢?