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