笔记整理~
前言:
用户
既存在PUBLIC/私有DBLINK的原因如下:
原
test_3用户的
存储过程一直访问
CWJK_3用户的
DB_
CWJK_3 public dblink ;
后将
CWJK_3用户的数据复制到
CWJK_2,
test_3
用户的数据复制到
test_2
;
test的2个用户都正常使用,想实现新用户
test_2访问
CWJK_2的数据,
其他用户正常访问
DB_
CWJK_3
public的dblink;
由于test_2用户复制的test_3的存储过程,
新建dblink
DB_
CWJK_2,,所以需要在
test_2创建一个私有dblink来访问
DB_
CWJK_2
;
测试结果:会优先选择私有DBLINK
测试如下:
SQL> SELECT * FROM dba_db_links where db_link='
DB_
CWJK_3
';
OWNER DB_LINK USERNAME HOST CREATED
------------- ---------------- -------------- -----------------------
PUBLIC DB_
CWJK_3
CWJK_3 (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.49)(PO 2014/8/13 1
SQL> SELECT * FROM user_users@db_
cwjk_3;
USERNAME
-----------
CWJK_3
SQL>
SQL> conn
test_2/
test_2@192.168.0.49:1521/
db
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as
test_2@192.168.0.49:1521/db
SQL> create database link db_
cwjk_3 connect to
cwjk_2 identified by
cwjk_2 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.49)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = fescotestdb)))';
Database link created
SQL> SELECT * FROM user_users@db_
cwjk_3;
USERNAME
-----------
CWJK_2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1841010/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-1841010/