Create Database link (dblink) in Oracle

PRIVATE:

Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.

PUBLIC:

Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

GLOBAL:

Creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.

To get TNS details using cmd prompt :

C:\Users\rajam>tnsping XE

Create Database Link:

Method 1:

CREATE DATABASE LINK ORCL_TO_XE CONNECT TO scott IDENTIFIED BY "tiger" USING 'XE';

Method 2:

CREATE DATABASE LINK ORCL_TO_XE
CONNECT TO scott
IDENTIFIED BY "tiger"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = RAJAM-IN.in.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))';


Dictionay tables:

DBA_DB_LINKS --> Lists all database links in the database.
ALL_DB_LINKS --> Lists all database links accessible to the connected user.
USER_DB_LINKS --> Lists all database links owned by the connected user.

Dropping a Database Link: 


Private:
The following statement drops the private database link named ouapp_to_fapap.
DROP DATABASE LINK ouapp_to_fapap; 

Public:
We must specify PUBLIC to drop a PUBLIC database link.
The following statement drops the public database link named ouapp_to_fapap.
DROP PUBLIC DATABASE LINK ouapp_to_fapap;