Database Link

Database Link

Database Link:- Database Link is a Database Object ,that is used for accessing one database from another database.In other word database link is a schema object in one database permit you to access objects on another database.

Type of Database Link:- There are 2 types of Database Link

1) Private Database Link
2) Public Database Link

Where we can Use:- We can access table,view and other PLSQL objects by using database link.Also we can perform DML operations by using database link.
                                    
1) Private Database Link:- Private Database Link are database link that can be accessed only by creator or you can say visible for only creator.
Syntax :-

CREATE DATABASE LINK <db_link_name>
CONNECT TO <user> IDENTIFIED BY <password>
USING '<remote_db_name>';

Example :- There are 2 Database have below tns entries

prod1db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.128)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )


prod2db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.129)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  ) 
 

Below create statement will create database link from prod1db to prod2db

CREATE DATABASE LINK prod2ln
CONNECT TO system IDENTIFIED BY <password>
USING 'prod2db';

same can be created by below statement as

CREATE DATABASE LINK prod2ln
CONNECT TO system IDENTIFIED BY <password>
USING '(DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.129)(PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = ORCL))
       )';

                       
2) Public Database Link :-Public Database Link  are database link that can be accessed by any user or you can say visible to all users.

Syntax :-

CREATE PUBLIC DATABASE LINK <db_link_name>
CONNECT TO <user> IDENTIFIED BY <password>
USING '<remote_db_name>';
Example:-

CREATE PUBLIC DATABASE LINK prod2pln
CONNECT TO system IDENTIFIED BY <password>
USING 'prod2db';

Example of using DATABASE Link:-
Example1:- Create a table on primary database with same structure and data available on remote database using database link.

CREATE TABLE EMPLOYEE AS SELECT * FROM EMPLOYEE@prod2pln;

Example2:- How can I see tables available in remote database using database link.

SELECT * FROM TAB@prod2pln;

Example3:- Delete records from EMPLOYEE table which is available at remote database using database link.

DELETE FROM EMPLOYEE@prod2pln;

Example3:-Update salary by 10000 of HR department employee , table is available at remote database

UPDATE EMPLOYEE@prod2pln set salary=10000 where department='HR';