SQL Queries : Database Links

Database link is a db object which enables the access of objects between databases. Databases that are linked can be oracle or different one.

Steps to Create Database Link

Step 1: Create an entry in tnsnames.ora file for service naming.
test_link =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
Note : If you are working in Client - Server setup for database then create on at server as well

Step 2: Connect to database with DB Admin user privileges.

Conn dbuser/dbpwd

Step 3: Create database link.

CREATE DATABASE LINK TEST_DB_LINK
USING 'TEST_LINK';

Step 4: Using database links.

SELECT * FROM EMP@TEST_DB_LINK;


Note: To upgrade the database links existing in the schema execute the below statement
EXEC DBMS_DBLINK.UPGRADE;