Saturday, November 5, 2011

How to drop/create Database Link from another schema?

SQL> CREATE or replace PROCEDURE scott.create_db_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';
END create_db_link;
2 3 4 5
6 /

Procedure created.

SQL> show user
USER is "SYS"
SQL> exec scott.create_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
SCOTT LINK1 SCOTT testdb 04-NOV-11

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


SQL> CREATE PROCEDURE scott.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link LINK1';
END drop_db_link; 2 3 4
5 /

Procedure created.

SQL> exec scott.drop_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

no rows selected

SQL>

5 comments:

Unknown said...

Really good one :-)

DOT said...

Very useful.
Many thanks.

Anonymous said...

find solutions here
http://solutions-ever.blogspot.com/2014/06/how-to-create-drop-db-link-in-oracle.html

Anonymous said...

Thanks, very handy..

Mediis said...

Thanks... I was forgetting the exec.