Database Link ( DBlink ) in Oracle

A Database Links is used to connect the one Oracle Database Server to other Database Server. It’s save in data dictionary. Through database link you can access the data of remote database. It is one way path to access information from other database in your database by network.

Database links created with public or private way:
Public: User called PUBLIC, all database users have access to remote database.
Private: user who created the links and access to remote database.

REMOTE_OS_AUTHENT parameter operates as follows:
TRUE: An external authenticated user can connect to remote database using a connected user database link.
FALSE: An external authenticated user can not connect to remote database until a network authentication service option is used.

Create Public DB Link

--Called Connected user , no username and password
CREATE PUBLIC DATABASE LINK dblink_salesdb USING 'salesdb';

--called fixed user, username & password is part of statement
CREATE PUBLIC DATABASE LINK dblink_salesdb CONNECT TO username IDENTIFIED BY password USING 'salesdb';

You need to check the connectivity of SALES1 through TNSPING command that its working fine before creating the database link.

tnsping salesdb

Create & Verified Private DB Link

CREATE DATABASE LINK dblink_salesdb CONNECT TO username IDENTIFIED BY password USING 'salesdb';
---Access data through DB Link
SELECT * FROM schema.object_name@connection;
--Example:
SELECT * FROM hr.emp@salesdb;

Check the database link present in database

SELECT * FROM DBA_DB_LINKS;

SELECT db_link, username, password, host, created FROM DBA_DB_LINKS;

Drop the Database Link

DROP DATABASE LINK link_name;

Drop public database link link_name;

Script for drop all database links present in Oracle

select 'Drop database link '||DB_LINK||' ;'  from dba_db_links where host like '%SKAS%';

Create DDL of database link in Oracle

select 'create database link ' || name ||' connect to '|| userid ||' identified by ' || password || ' using '||''''||host ||''''||';' from sys.link$;

SELECT db_link, username, password, host, created FROM user_db_links;

Determine which connection is open

COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"
SELECT * FROM V$DBLINK

Check first and last login time for Database link

SELECT DB_NAME, HOST_NAME, FIRST_LOGON_TIME, LAST_LOGON_TIME
FROM DBA_DB_LINK_SOURCES;