Tales From A Lazy Fat DBA

Database Links are objects which we create to establish a connection from one database to another. They are classified in to three parts:

1. Private Database Links: Belongs to specific schema. Only the owner of the link can use it and is default DB link type.
2. Public Database Links: Belongs to all users exists in Database.
3. Global database link – Defined in an OID or Oracle Names Server. Anyone on the network can use it.
Conditions:
1. User should have Privilege ‘CREATE DATABASE LINK’.

Format:
Create Database Link <DB Link> connect to < User > identified by < Password > using < Service Name >;
OR
Create Public Database Link <DB Link> connect to < User > identified by < Password > using < Service Name >;

Example:

Create Database Link <DB Link Name> connect to < User > identified by < Password > using < Service Name >;
SQL> create database link larry1 connect to larry identified by oracle90 using ‘ORCL’;
Database link created.

SQL> select * from dual@larry1;

D
–
X

SQL> update etr@larry1 set ID=7  where NAME=’prashant’ and TEAM=’cis’;

1 row updated.

SQL> select * from etr@larry1;

NAME         TEAM       ID
———— ———- —
prashant     cis        7
shantanu     cis        12
saurabh      oracle     19
ram          helpdesk   3
prashant     unix       5
tondu        security   1
amit         qa

7 rows selected.