Oracle Database (DB) Link

Database link is a schema object that allows you to access objects in a remote database. Oracle allows users to access tables, views, or other objects from one Oracle database from within another database. 

DB link support select, insert, update, and delete on another remote databases like Oracle, PostgreSQL, MySQL, and etc.

 

Types of DBLinks:

1. Public DBLink: Accessible by all users in the database.
2. Private DBLink: Only accessible by the user who created it.
3. Global DBLink: Defined in an Oracle networking environment and typically used in conjunction with Oracle's directory services.

Public DB Link:

With TNS Entry:

CREATE PUBLIC DATABASE LINK link_name CONNECT TO remote_user_name IDENTIFIED BY remote_user_password USING 'remote_service_name';

Without TNS Entry:

CREATE PUBLIC DATABASE LINK LINK_PEARL
CONNECT TO aadhavan IDENTIFIED BY aadhavan04042024
Using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PEARL)
)
)';

With EZconnection:

CREATE PUBLIC DATABASE LINK pearl_link_pub CONNECT TO siva IDENTIFIED BY siva123 USING '192.168.31.203:1521/PEARL';

Private DB link:

CREATE DATABASE LINK pearl_link CONNECT TO muthu IDENTIFIED BY muthu123 USING 'mypearl';

List out the DBLink:

select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;

select and DML for the target database tables:

SELECT * FROM remote_table@my_dblink;
SELECT column1, column2 FROM remote_table@my_dblink;
UPDATE remote_table@my_dblink SET column1 = value WHERE condition;
DELETE FROM remote_table@my_dblink WHERE condition;

Using a synonym:

CREATE SYNONYM test_dblink FOR remote_table@database_link;
SELECT * FROM test_dblink;

Drop the DBlink:

drop database link REMOTE_PRIVATE1;
drop public database link TEST_REMOTE2;