o7planning

Oracle Database Link and Synonym Tutorial with Examples

  1. What is Database Link?
  2. Create Database Link
  3. Access objects via Database Link
  4. Oracle Synonym
  5. Database Link to None-Oracle database

1. What is Database Link?

Database Link is an object in SCHEMA of oracle, it likes a bridge to connect other database which help you to access objects of the other database.
You can also create an Database Link to connect Oracle to another database, such as MySQL, SQL Server, ... in this case you need to use Oracle Heterogeneous Service

2. Create Database Link

Here I illustrates the connection of 2 Oracle database systems located on two different computers.
tnsname.ora
Firstly, you need to understand that tnsnames.ora file of Oracle.

tnsnames.ora is a file described Oracle database, you can find this file in the installation directory of Oracle.
  • <ORACLE_HOME>\product\12.1.0\dbhome_1\NETWORK\ADMIN
The content of file tnsname.ora is same as illustration below.
Here are images depicted the two Oracle Database systems located on 2 different computers.
Creating a Database Link named mydblink to connect from PROD1 to PROD2.
Create Database Link
-- 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)
)
)';
On PROD1, you can test the newly created Database Link by the command:
Select * from Dual@mydblink;
There are several VIEW helps you to query a list of Database Link on the database:
-- 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 ;
Example:
-- 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 (continue)
If the description of the Database 2 has been declared in the tnsname.ora file of database1, you can create Database Link from database1 to database2 with more concise syntax.
-- 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';

3. Access objects via Database Link

Once you have the Database Link, you can access to the objects in the Database2 through Database Link.
-- 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 .. ;
 

4. Oracle Synonym

If you do not want to add @dblinkName when accessing an object through a Database link, you can create a Synonym for that object.
Create Synonym:
-- Create Synonym:

create or replace Synonym  My_Table_Syn for My_Table@mydblink;
Note: You can also create Synonym for functions, procedures.

5. Database Link to None-Oracle database

  • TODO: