创建db_link

准备工作：
数据库：192.168.60.131 dup
192.168.60.132 dup1
192.168.60.131：
$ cat tnsnames.ora
dup1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dup1)
(SERVER = DEDICATED)
)
)
192.168.60.132：
cat listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.132)(PORT = 1521))
)

测试：
在192.168.60.131上
$ sqlplus scott/tiger@dup1

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 20 02:25:06 2018

Copyright © 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

在192.168.60.131上创建用户test1和test2
SQL> create user test1 identified by oracle;

User created.

SQL> c/1/2
1* create user test2 identified by oracle
SQL> /

User created.

SQL> grant resource,connect to test1,test2;

Grant succeeded.

赋予test1用户创建db_link的权限
SQL> grant create public database link,create database link to test1;

Grant succeeded.

实验一：
在192.168.60.131上创建公有的固定用户的db_link
SQL> conn test1/oracle
Connected.
SQL> create public database link test1_pub_scott
connect to scott identified by tiger
using ‘dup1’;

SQL> select count(*) from emp@test1_pub_scott;

COUNT(*)

---

14

或者
SQL> select count(*) from scott.emp@test1_pub_scott;

COUNT(*)

---

14

SQL> conn test2/oracle
Connected.
SQL> select count(*) from emp@test1_pub_scott;

COUNT(*)

---

14

实验二：
在192.168.60.131上创建公有的连接/当前用户的db_link

SQL> conn test1/oracle
Connected.
SQL> CREATE PUBLIC DATABASE LINK test1_pub_current USING ‘dup1’ ;

Database link created.

用test1用户创建了公有的当前用户的db_link，那么test1用户可以远程操作数据库的用户test1并且密码为oracle的所有对象；test2用户也可以远程操作数据库的用户test2并且密码和本地test2用户一样的所有对象

创建私有的db_link只需要去掉public即可。

SQL> select * from dba_db_links;
select owner,object_name from dba_objects where object_type=‘DATABASE LINK’;

It is a private fixed user database link
it is a private current user database link
it is a private connected user database link