Oracle Dblink Preliminary use

+---------+
| DB1 |
+---------+
--Create user and authorize
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 Creating a Test table
CREATE TABLE T1 as select * from Scott.emp;
CREATE TABLE t2 as select * from Scott.dept;

--U2 Creating a Test table
CREATE TABLE U2_t1 as SELECT * from Scott.emp;

--Create U1 local synonyms

Create public synonym synonym_t1 for u1.t1;

Create public synonym synonym_t2 for u1.t2;

--Login verification with U2

Conn U2/U2
SELECT * from Synonym_t1;

SELECT * from Synonym_t2;

+---------+
| DB2 |
+---------+
In the Hosts file, join the DB1 record as follows
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;

--Create 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)
)
)‘;

--Create a remote U2 synonym

Create public synonym synonym_u2_t1 for [email protected]; (U2 own table)

Create public synonym synonym_u2_t2 for [email protected]; (U2 synonyms)

--Verification

SELECT * from Synonym_u2_t1;
SELECT * from Synonym_u2_t2;

Oracle Dblink Preliminary use