Tales From A Lazy Fat DBA

Den of an Oracle DB Performance & Troubleshooting freak … \,,/

  • Likes

    • 199,506
  • Archives

  • Categories

  • Subscribe

Database Links Explained ?

Posted by FatDBA on October 7, 2012

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.

One Response to “Database Links Explained ?”

  1. hari said

    hai good explanation.how to create a global link .u told gobal links means any one can use that link with in that network.can u elablorate ur xplanation plz.

Leave a Reply to hari Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: