SQL 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
CONNECT TO IDENTIFIED BY
USING '';

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
USING 'prod2db';

same can be created by below statement as

CREATE DATABASE LINK prod2ln
CONNECT TO system IDENTIFIED BY
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
CONNECT TO IDENTIFIED BY
USING '';
Example:-

CREATE PUBLIC DATABASE LINK prod2pln
CONNECT TO system IDENTIFIED BY
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';

ALTER DATABASE LINK :- Alter database link statement is used to update the change password.

Syntax :- ALTER DATABASE LINK
          CONNECT TO IDENTIFIED BY ;

Example:-
ALTER DATABASE LINK prod2ln
  CONNECT TO system IDENTIFIED BY system_new_password;

ALTER PUBLIC DATABASE LINK prod2pln
  CONNECT TO system IDENTIFIED BY system_new_password;
 

DROP PRIVATE DATABASE Link :- Below statement is used to drop the private database link.

DROP DATABASE LINK prod2ln;

DROP PUBLIK DATABASE Link :- below statement is used to drop the public database link.

DATA DICTIONARIES for DATABASE LINK :- Below are the DATA DICTIONARIES for DATABASE LINK.