Wednesday, February 15, 2012

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)
    )
  )


2nd 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$;


3 comments:

  1. Thanks Waseem. It's a great post!

    ReplyDelete
  2. hello wasim! could u plz share ur contact infor or plz call/text me at 9313404334. if possible

    ReplyDelete