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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s