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;
