oracle dblink初步使用

+---------+
| db1      |
+---------+
--创建用户并授权

sqlplus / as sysdba
create user u1 identified by u1 account unlock default tablespace USERS;
create user u2 identified by u2 account unlock default tablespace USERS;
grant connect,resource to u1;
grant connect,resource to u2;
grant create public synonym to u2;
grant select on u1.t1 to u2;
grant select on u1.t2 to u2;

--u1创建测试表

create table t1 as select * from scott.emp;
create table t2 as select * from scott.dept;

--u2创建测试表

create table u2_t1 as select * from scott.emp;

--创建u1本地同义词

create public synonym synonym_t1 for u1.t1;
create public synonym synonym_t2 for u1.t2;

--用u2登录验证

conn u2/u2
select * from synonym_t1;
select * from synonym_t2;

+---------+
| db2      |
+---------+
在hosts文件在加入db1的记录，如下所示

192.168.1.180 testnode1.osdba.org testnode1

sqlplus / as sysdba
create user u3 identified by u3 account unlock default tablespace USERS;
grant connect,resource to u3;
grant create database link to u3;
grant create public synonym to u3;

--创建dblink

create database link dbtest1 connect to u2 identified by u2
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.osdba.org)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb1)
)
)';

--创建远程u2同义词

create public synonym synonym_u2_t1 for u2_t1@dbtest1; (u2拥有表)
create public synonym synonym_u2_t2 for synonym_t2@dbtest1; （u2同义词）

--验证

select * from synonym_u2_t1;
select * from synonym_u2_t2;