Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to drop db link in other schemas as sys user ??

872581Aug 31 2011 — edited Jun 15 2012
Hi, all.

I would like to drop db links in other schemas as sys user.

I got the following error when droping a link as sys user.

-------------------------------------------------------------------------------------
papa2:SQL> drop database link kht."TTT";
drop database link kht."TTT"
*
ERROR at line 1:
ORA-02024: database link not found
-------------------------------------------------------------------------------------

Anyone can help me??

Thanks in advance.
Best Rregards.
This post has been answered by sb92075 on Aug 31 2011
Jump to Answer

Comments

sb92075
Answer
869578 wrote:
Hi, all.

I would like to drop db links in other schemas as sys user.

I got the following error when droping a link as sys user.

-------------------------------------------------------------------------------------
papa2:SQL> drop database link kht."TTT";
drop database link kht."TTT"
*
ERROR at line 1:
ORA-02024: database link not found
-------------------------------------------------------------------------------------

Anyone can help me??

Thanks in advance.
Best Rregards.
there is no silver bullet

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin004.htm#i1008271

"If the link is private, then it must be in your schema. "
Marked as Answer by 872581 · Sep 27 2020
user12011194
declare

procedure execute_sql( p_cmd varchar2, p_user varchar2 ) is

l_user_id number;

l_cursor number;

l_result number;

begin

-- fetch userid

select user_id into l_user_id

from dba_users

where username = p_user;

-- parse and execute

l_cursor := sys.dbms_sys_sql.open_cursor;

sys.dbms_sys_sql.parse_as_user( l_cursor, p_cmd, dbms_sql.native, l_user_id, true );

l_result := sys.dbms_sys_sql.execute( l_cursor );

sys.dbms_sys_sql.close_cursor( l_cursor );

end;

begin

execute_sql( 'drop database link NUME_DBLINK', 'NUME_USER' );

end;

/


Sorin
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 13 2012
Added on Aug 31 2011
2 comments
513 views