Friday, 21 June 2013

Create/Drop database link from another schema

Create DB Link from another schema:-

SQL> CREATE or replace PROCEDURE DATALOAD.create_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'create database link VSNLDEV1 connect to DATALOAD identified by xxxxx using ''CRAMER''';
 END create_db_link;
 /

Procedure created.

SQL> show user
 USER is "SYS"



SQL> exec DATALOAD.create_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
OWNER                          DB_LINK              USERNAME             HOST                 CREATED
 ------------------------------ -------------------- -------------------- -------------------- --------
 DATALOAD                       VSNLDEV1             CRAMER               VSNLDEV1             13-06-08



SQL> drop database link DATALOAD.VSNLDEV1;
 drop database link DATALOAD.VSNLDEV1
 *
 ERROR at line 1:
 ORA-02024: database link not found



Drop DB Link from another schema:-

SQL> CREATE PROCEDURE DATALOAD.drop_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'drop database link VSNLDEV1';
 END drop_db_link;
 /

Procedure created.

SQL> exec DATALOAD.drop_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
no rows selected

SQL>

For dropping multiple database links:-

 

No comments:

Post a comment