Create / Drop / Check Database link or DB link

Two ways to create DB link in 11g Database

1. With TNS entry in tnsnames.ora

2. TNS entry given at the time of db link creation.

SQL> desc DBA_DB_LINKS

 Name                                      Null?                              Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL      VARCHAR2(30)

 DB_LINK                                   NOT NULL       VARCHAR2(128)

 USERNAME                                                                 VARCHAR2(30)

 HOST                                                                          VARCHAR2(2000)

 CREATED                                   NOT NULL     DATE

***FIND ALL DBLINKS created in database:

select * from dba_db_links;

Create DB link

Syntax:

Create public/private database_link linkname connect to username identified by Password using ‘Service Name’; i

Example :

1st method: Create DBLINK with tns entry made in tnsnames.ora

CREATE PUBLIC DATABASE LINK "TO_SPOT.WORLD" CONNECT TO CFS_SPOT IDENTIFIED BY CFS_Passwd_123 USING 'Spotprof.was.abc.com’;

Now make an entry in tnsnames.ora present in $TNS_ADMIN

SPTPS.WAS.ABC.COM

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = abc.xyz.was.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = SXXX

      (SERVER = DEDICATED)

    )

  )

2^nd Method:

conn / as sysdba issue this command on sqlprompt

CREATE public DATABASE LINK TO_SPOT CONNECT TO CFS_SPOT IDENTIFIED BY password USING

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))

(CONNECT_DATA=(SID = SXXX')));

Check DB link is working or not

conn / as sysdba or conn to sql and try the below one :

syntax : select * from dual@linkname;

Example

select * from dual@TO_SPOT;

DROP DBLINK:

DROP PUBLIC DATABASE LINK "TO_SPOT.WORLD";

truncate /drop all database links

truncate table link$;