Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Select Value$ From Props$ Where Name = 'global_db_name'

Select Value$ From Props$ Where Name = 'global_db_name'

Unable to drop a database link


Subject: Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 27, 2006, 16:06, 5572 days ago
Os info: Sun Solaris
Oracle info: 9206
Message: Hi,

I am unable to drop some private database links.
I get the errors:
ORA-02024: database link not found

Yet, when I query the DBA_DB_LINKS view I find these database links.

Any idea?
Thanks for any feedback.

Regards.

Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Unable to drop a database link
Author: Michel Cadot, France
Date: Sep 27, 2006, 16:09, 5572 days ago
Message: Hi,

You can only drop your database links (and public ones if you are allowed to).
No way to drop another one's.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re-Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 27, 2006, 16:11, 5572 days ago
Message: Hi Michel,

Yes, I agree. But I can not drop them when I am logged as the owner of the link.

Regards.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Michel Cadot, France
Date: Sep 27, 2006, 16:15, 5572 days ago
Message: Hi Laurent,

And the error is?

To better understand, copy and paste the select on dba_db_links and your drop statement.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re-Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 27, 2006, 16:25, 5572 days ago
Message: Hi,

From dba_db_links:
select * from dba_db_links where db_link ='DBOPBNLC':

LPDB,DBOPBNLC,BOLUSER,(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS=(COMMUNITY=TCP.bag)(PROTOCOL=TCP)(Host=145.228.238.176)(Port=1486)) )
(CONNECT_DATA =
(SID = DBOPBNLC)
)
),3/4/2005 9:03:58 PM

logged under LPDB user:
SQL> drop database link DBOPBNLC;
ORA-02024: database link not found

The global name has been changed before the attempt to drop the link. Could this be the reason?? If yes, how can I bypass that issue?

Regards.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 27, 2006, 16:32, 5572 days ago
Score: Score: 100 Pts Score: 100 Pts Score: 100 Pts Score: 100 Pts Score: 100 Pts
Message: Are you sure this is not a public dblink?
If it you need to supply public keyword to the drop statement

Alessandro

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Bruno Vroman, Belgium
Date: Sep 27, 2006, 16:39, 5572 days ago
Score: Score: 200 Pts Score: 200 Pts Score: 200 Pts Score: 200 Pts Score: 200 Pts
Message: Hi Laurent,

you're right: Note:382994.1
Cannot drop a database link after changing the global_name ORA-02024

... Looks quite bad :-(

Good luck,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 27, 2006, 16:44, 5572 days ago
Message: what about changing the global_name to the old value just the time to drop the db link?

Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Hemant Patel, United States
Date: Sep 28, 2006, 06:49, 5572 days ago
Message: Hi Alessandro,

It is possible with some more pain of work.
Hemant.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 28, 2006, 09:32, 5571 days ago
Score: Score: 200 Pts Score: 200 Pts Score: 200 Pts Score: 200 Pts Score: 200 Pts
Message: i did it without problems in 8.1.7.4 but using this tecnique:

UPDATE GLOBAL_NAME SET GLOBAL_NAME = '<OLDGLOBALNAME>';
ALTER DATABASE RENAME GLOBAL_NAME TO OLDGLOBALNAME;
drop the db link;
UPDATE GLOBAL_NAME SET GLOBAL_NAME = '<NEWGLOBALNAME>';
ALTER DATABASE RENAME GLOBAL_NAME TO NEWGLOBALNAME;

note that i don't raise the commit after the update, the commit is raised implicitly by the alter database

Laurent, can you test this in a non-production enviroment?

Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Hemant Patel, United States
Date: Sep 28, 2006, 09:38, 5571 days ago
Message: Hi Alessandro,

Thanks for your update/interest in this thread.

Hemant.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re-Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 28, 2006, 09:52, 5571 days ago
Message: Hi,

Thanks all for the answers!!
Unfortunately even when I update props$ (or global_name), althouhg I can view the update, when I log on to the user from which I want to drop the db links, I still "see" from this user the old value of the global_name.
At that stage, I have no more inspiration...
Any help will be welcome.

Thanks and regards.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 28, 2006, 11:01, 5571 days ago
Score: Score: 600 Pts Score: 600 Pts Score: 600 Pts Score: 600 Pts Score: 600 Pts
Message: Hi Laurent,
it is quite strange because i tested my solution in a 9i(9.2.0.6) non-production enviroment and works well, let me show:

conn username/password
SQL>select * from global_name;
MYNAME
SQL>create database link testing
connect to <username>
identified by <password>
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP) (HOST = <your host>) (PORT = <your port num>))) (CONNECT_DATA = (SID = <your sid> )))'
db link created
SQL>select * from all_tables@testing;
db link works (tested)
SQL>disconn
conn as sysdba
SQL>UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'MYNAME.WORLD';
SQL>ALTER DATABASE RENAME GLOBAL_NAME TO MYNAME.WORLD;
SQL>disconn
conn username/password
SQL>drop database link testing;
ORA-02024: database link not found
SQL>disconn
conn as sysdba
SQL>UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'MYNAME';
SQL>ALTER DATABASE RENAME GLOBAL_NAME TO MYNAME;
SQL>disconn
conn username/password
SQL>drop database link testing;
dropped successfully

Regards
Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re-Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 28, 2006, 19:53, 5571 days ago
Message: Hi Alessandro,

Yes, this is very good what you wrote and this is also what I have done. Yet I still can not drop the database link.
Anyway, thanks for the help.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 29, 2006, 12:39, 5570 days ago
Message: very strange, because i tested on 9.2.0.6 version of Oracle so the same that you are using but same OS too (Solaris)

Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 29, 2006, 13:28, 5570 days ago
Message: mmhhh, do you forget to change again the value on the prop$ table?

Check if those values are identical:
select * from global_name;
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME';

Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re-Unable to drop a database link
Author: Lauri, Netherlands
Date: Sep 29, 2006, 13:53, 5570 days ago
Message: Hi Alessandro,

Strange indeed.
When I issue:
update global_name set...
and
alter database rename global_name...
the SYS.PROPS$ is already updated.

Regards.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here
Subject: Re: Re-Unable to drop a database link
Author: Alessandro Deledda, Italy
Date: Sep 29, 2006, 14:24, 5570 days ago
Message: Ok, but i suggest to start from a clean situation where global_name and GLOBAL_DB_NAME return exactly the same value, then at that point try to forget about sys.prop$ table and apply my solution

Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page
If you think this item violates copyrights, please click here

Select Value$ From Props$ Where Name = 'global_db_name'

Source: https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=26011&DestinationA=RSS