Recreating the Database Link

Hi,

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_name                              string      DEV


SQL> select * from dba_db_links where db_link like 'CMS_LINK.ABC.ORG';

OWNER                          DB_LINK                             USERNAME                       HOST                                          CREATED
------------------------------ ----------------------------------- ------------------------------ --------------------------------------------- ---------
CSR                            CMS_LINK.ABC.ORG                CSR                            CMS                                           25-DEC-14


SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','CMS_LINK.ABC.ORG','CSR') FROM dual;

DBMS_METADATA.GET_DDL('DB_LINK','CMS_LINK.ABC.ORG','CSR')
--------------------------------------------------------------------------------

  CREATE DATABASE LINK "CMS_LINK.ABC.ORG"
   CONNECT TO "CSR" IDENTIFIED BY VALUES '05B57442670C49D297101E6A84'
   USING 'CMS'


SQL> drop database link CMS_LINK.ABC.ORG;
drop database link CMS_LINK.ABC.ORG
                   *
ERROR at line 1:
ORA-02024: database link not found


SQL> CREATE PROCEDURE csr.drop_db_link AS
  2  BEGIN
  3  EXECUTE IMMEDIATE 'drop database link CMS_LINK.ABC.ORG';
  4  END drop_db_link;
  5  /

Procedure created.

SQL> exec csr.drop_db_link

PL/SQL procedure successfully completed.


SQL> CREATE PUBLIC DATABASE LINK "CMS_LINK.ABC.ORG"
  2     CONNECT TO "CSR" IDENTIFIED BY VALUES '05B57442670C49D297101E6A84'
  3     USING 'CMS';

Database link created.


SQL> select * from dba_db_links where db_link like 'CMS_LINK.ABC.ORG';

OWNER                          DB_LINK                             USERNAME                       HOST                                          CREATED
------------------------------ ----------------------------------- ------------------------------ --------------------------------------------- ---------
PUBLIC                         CMS_LINK.ABC.ORG                CSR                            CMS                                           13-MAR-15

Happy Learning :)