Skip to content
Advertisements

Drop database link using sys

Step 1:

select * from dba_db_links

Step 2:  create a procedure

Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is
plsql varchar2(1000);
cur number;
uid number;
rc number;
begin
select
u.user_id into uid
from dba_users u
where u.username = schemaName;
plsql := ‘drop database link “‘||dbLink||'”‘;
cur := SYS.DBMS_SYS_SQL.open_cursor;
SYS.DBMS_SYS_SQL.parse_as_user(
c => cur,
statement => plsql,
language_flag => DBMS_SQL.native,
userID => uid
);
rc := SYS.DBMS_SYS_SQL.execute(cur);

SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/

 

Step 3: Drop database link using sys

 

exec Drop_DbLink(‘owner’,’dblinkname’);

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: