Oracle Database links.

Primary database name = dell
Secondary Database Name = dup

On Secondary

Create TNS Entry for primary database.

[oracle@newyork admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

REMOTEDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.39)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dell)
    )
  )



Login as sys

[oracle@newyork ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 19:21:11 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> grant create database link to scott;
SQL> grant create database link to hr;

Grant succeeded.


Private connected user database link

SQL> conn scott/tiger;
Connected.

SQL> create database link mylink1 using 'remotedb';

Database link created.

SQL> select * from emp@mylink1 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-DEC-81       9500
        30


SQL> select * from user_db_links;

DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME                       PASSWORD
------------------------------ ------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
MYLINK1.EXAMPLE.COM

remotedb
26-DEC-12



Private fixed user database link

Connect as HR user.

SQL> conn hr/hr
Connected.


SQL> create database link mylink2
  2  connect to scott identified by tiger using 'remotedb';

Database link created.


SQL> select * from scott.emp@mylink2 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-DEC-81       9500
        30


SQL> select * from user_db_links;

DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME                       PASSWORD
------------------------------ ------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
MYLINK2.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12


Public fixed user database link.

SQL> conn / as sysdba
Connected.

SQL> create public database link mylink3
  2  connect to scott identified by tiger using 'remotedb';

Database link created.


SQL> conn tom/jerry
Connected.

SQL> select * from scott.emp@mylink3 where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-DEC-81       9500
        30




SQL> select * from dba_db_links;

OWNER
------------------------------
DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
PUBLIC
MYLINK3.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12

SCOTT
MYLINK1.EXAMPLE.COM

remotedb
26-DEC-12

HR
MYLINK2.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12




Dropping database links.

DROP [PUBLIC] DATABASE LINK link_name;

Note: You cannot drop a database link in another user's schema and you have to 
specify PUBLIC option in order to drop a public database link.