Dropping and Recreating DB-Link in Oracle

Hi Guy's,

Its very simple in oracle to drop and recreate the db-link pls find the steps.

Task :-

• Drop the existing db-link and recreate with the same name.

Steps :-

• First take out the DDL of existing db-link to get the db-link details like name of db-link,remote user,db-port,service name of remote database which you are connecting..
• When i extracted Metadata for db-link i found 4 db-links which are created in my database.
• I will drop 'EYES' db-link and recreate with the same name as per our requirement..

SQL> Set long 1000
SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

DBMS_METADATA.GET_DDL('DB_LINK',DB.DB_LINK,DB.OWNER)
--------------------------------------------------------------------------------

  CREATE PUBLIC DATABASE LINK "DMS.COM"
   USING 'DMS'


  CREATE PUBLIC DATABASE LINK "EYES"
   CONNECT TO "FMIS" IDENTIFIED BY VALUES '06C652AFA6A72ADA2EFCFDF0850C4A2B817A8
F1EB7D9D01CC4C88940C63D492915BD2C9F6AECD7367C944154F377700E299604BB91A4E369497B9
6540C66A084BBD808BB7EB68C7956108A189BE969CB0D968F54C729FDA4EDF037204EDFF0F4D72E2
B4EC7F08657089E2CF64AB3FD4B374A63A7FB4C6EF66D53F62D22001C39'
   USING 'ORADB1.iecc.md:1521/HRLIVE'

  CREATE PUBLIC DATABASE LINK "MCC.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05CF926A55876BECA1276B20465CE75269BB3
45571ACAF806C'
   USING 'MCCDB'

  CREATE PUBLIC DATABASE LINK "MCCLOCAL.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05791082DCF9BBB5C0492C52008E0CFAF1'
   USING 'MCCLOCAL'

                                                                          *********
                                                               

 SQL> DROP PUBLIC DATABASE LINK eyes;

 SQL> CREATE PUBLIC DATABASE LINK EYES CONNECT TO fmis IDENTIFIED BY fmis
USING 'ORADB1.iecc.md:1521/HRLIVE';


Happy Learning...