Database Link (DB Link)

How to create DB Link

SQL> create public database link troy connect to troy identified by martin using 'cost1';

SQL> create database link tm connect to troy identified by martin using 'cost1';

A DB Link is a schema object that helps us to fetch objects of a schema from another database

Public DB Link can be accessed by any database user:-
           Using this DB Link any user of this database(cosp1) can access all the objects in troy's schema, as well as the objects on which troy has access to in cost1 database.
Note: We need to have troy's password which is used in cost1 to create this DB Link


SQL> select name from v$database;

NAME
---------
COSP1

SQL> create public database link troy connect to troy identified by martin using 'cost1';

SQL> select * from emp@troy;

EMPNO ENAME  JOB      MGR   HIREDATE  SAL     COMM     DEPTNO
----- ------ -------- ----- --------- ------ ------- ----------
7369 SMITH   CLERK    7902 17-DEC-80   800               20
7499 ALLEN   SALESMAN 7698 20-FEB-81  1600     300       30
7521 WARD    SALESMAN 7698 22-FEB-81  1250     500       30
7566 JONES   MANAGER  7839 02-APR-81  2975               20


SQL> select count(*) from sys.dba_objects@troy;

  COUNT(*)
----------
     72503

Note: On cost1 database troy has select privilege on dba_objects which belongs to sys schema, which now we can access from cosp1 database using DB Link named 'troy'.

A Private DB link, on the other hand is for exclusive use of the user who is creating it. 

SQL> create database link tm connect to troy identified by martin using 'cost1';

We cannot create Private DB Link using schema_name.object_name syntax.
 
SQL> create database link scott.troy1 connect to troy identified by martin using 'cost1';

Database link created.

SQL> select * from dba_db_links;

OWNER   DB_LINK     USERN  HOST     CREATED
------  ----------- -----  ------   ---------
SYS     SCOTT.TROY1 TROY   cost1    27-MAR-14
PUBLIC  TROY        TROY   cost1    27-MAR-14
SYS     TM          TROY   cost1    27-MAR-14

So we see the new DB Link created is owned by SYS and not by SCOTT as we intended.
Let's grant scott privilege to create DB Link and see how it works;

SQL> grant create database link to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create database link troy2 connect to troy identified by martin using 'cost1';

Database link created.

SQL> conn / as sysdba
Connected.

SQL> sho user
USER is "SYS"

SQL> select * from emp@troy2;
select * from emp@troy2
                  *
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> select * from dba_db_links;

OWNER  DB_LINK        USERN HOST         CREATED
------ -------------- ----- ---------- ---------
SYS    SCOTT.TROY1    TROY  cost1      27-MAR-14
PUBLIC TROY           TROY  cost1      27-MAR-14
SCOTT  TROY2          TROY  cost1      27-MAR-14
SYS    TM             TROY  cost1      27-MAR-14

So, now we have troy2 DB Link created and is owned by SCOTT and we see that it cannot be accessed by even SYS user, since its Private DB Link.

How to drop DB Link

In order to drop self-owned DB Link

SQL> drop database link scott.troy1;

In order to drop public DB Link 

SQL> drop public database link troy;

In order to to drop Private DB Link other than yours, ensure you have create any procedure privilege or login as sys or dba.

SQL> create procedure SCOTT.drop_link as

2 begin

3 execute immediate 'drop database link TTSP1.WORLD';

4 end;
5 /

Note: Ensure SCOTT should be replaced with the owner of the private database link and TTSP1.WORLD needs to be replaced by private database link that needs to be removed.

Next, execute the procedure with either privileges mentioned earlier;

SQL> execute SCOTT.drop_link;

Once procedure executed and private database link is dropped, please drop this procedure as best practice

SQL> drop procedure SCOTT.drop_link;