How to create a database link in Oracle

A database link creates a connection between a local database and a remote database. You might want to create a database link, for instance, if you want the data in a remote database updated when the local database is updated. Here’s how to do this:

1. Create the entry for the remote database in your tnsnames.ora eg:

REMOTE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.4.220)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REMOTE)
)
)

2. Now go to SQL*PLUS and create the db link

CREATE DATABASE LINK other_db CONNECT TO scott IDENTIFIED BY tiger USING ‘tns_alias’;

Give the DB link a good name. In my example we’ll call it ‘other_db’

You can test the database link with a simple query like the following:

SELECT sysdate FROM dual@other_db;

Select * FROM testable@other_db where rownum<100;

Leave a Reply