Oracle Database Link and Synonym Tutorial with Examples

-- On Prod1 schema.
-- Create a Database Link named mydblink
-- Connect to Prod2 schema on myserver2.

CREATE DATABASE LINK mydblink
CONNECT TO PROD2 IDENTIFIED BY "Prod2Password"
USING '(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVICE_NAME = db12c)
)
)';

Select * from Dual@mydblink;

-- All DB links defined in the database
Select * from DBA_DB_LINKS ;

-- All DB links the current user has access to
Select * from ALL_DB_LINKS ;

-- All DB links owned by current user
Select * from USER_DB_LINKS ;

-- Format column length.

column OWNER format a10
column DB_LINK format a10
column USERNAME format a10
column HOST format a10
column CREATED format a10

-- Query view: ALL_DB_LINKS:

Select * from ALL_DB_LINKS;

-- Create Database Link from prod1 schema of Database1 to prod2 schema of Database2.

create database link mydblink connect to
   prod2 identified by prod2password using 'PROD2_DB';

-- Query table of Database2:

Select * from My_Table@mydblink;

Select * from My_View@mydblink;

-- And Insert, Update, Delete,..

Insert into My_Table@mydblink .....;

Update My_Table@mydblink .. ;
 

-- Create Synonym:

create or replace Synonym  My_Table_Syn for My_Table@mydblink;