By having global names set to false in the init.ora (or dynamically) you will not have this problem. I remember that this was a rule for DB links to work in 8.0.x but there was nothing mentioned for 9i so I set it to true. After fixing it, my db_links work for a cloned database. Ruth -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman, Donald Sent: Friday, March 05, 2004 12:03 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: DB links Yes, I saw you previous post and tried it. I have mucked about with this before but I have multiple db's running on this server and one of them is our OMS. I didn't want to make a change while playing with a test db that would affect production stuff. I did try it briefly but it didn't fix my problem this time. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Cachito Reyes Pacheco Sent: Friday, March 05, 2004 10:31 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: DB links After we migrated to windows 2000 we have problems in db links, because server suffix so we fixed doing this update global_name set global_name='SID'; and set sqlnet.ora NAMES.DEFAULT_DOMAIN = WORLD ----- Original Message ----- From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, March 04, 2004 6:11 PM Subject: RE: DB links I want to append to this thread because I've been having many the same problems this week and couldn't get my answer out of this thread which I saved. I copied my production database and moved it to another server. I changed the global_name to xxxxx.standby using the ALTER DATABASE RENAME GLOBAL_NAME TO xxxxx command. I had an existing (copied) db link in this database copy. It stopped working because the 'standby' appended to the db_link. We restored the name on the copy by updating global_name. You can't change it back by using rename. It wants 'something.something' and will not name it back to 'something.' And, no, I don't recommend making any changes to the data dictionary this way and wouldn't do it on a production system. I tested the db link and it worked. I dropped the db link again, changed the name of the database back to xxxxx.standby and recreated the db_link and got the error caused by the database name being different than the link name plus global name. I altered session and set global_name to false; My error changed to a "TNSNames cannot resolve service name" I couldn't solve it. I created the db link again with the fully qualified host. That didn't work. CREATE PUBLIC DATABASE LINK xxx CONNECT TO xxxxxx IDENTIFIED BY xxxxxxx USING 'xxxxxxxxx = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxx)(PORT = 1521)) ) (CONNECT_DATA = (SID =xxxxxxx) (SERVER = DEDICATED) ) ) '; I dropped it, recreated the link again back to the db link alias. CREATE PUBLIC DATABASE LINK xxx CONNECT TO xxxxxx IDENTIFIED BY xxxxxxx USING 'xxxxxxxxx' It worked.(??) I think the key was that the link had to have been created after the 'alter session set global_name to false'; I'm not sure why the fully qualified host didn't work. I'm not positive what I did to fix it. I've reconstructed it as best I could. I thought this might be useful the next time somebody gets stuck. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ana Choto Sent: Thursday, February 12, 2004 10:52 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: DB links I'm not sure if you found a solution for this yet. But, one thing I had trouble with when using a link was that I was using the service name on my select statement instead of the name of the link's. i.e., select from xxx@servicename instead of select from xxx@dblink_name Thanks Ana E. Choto American University e-Operations - Information Technology Phone (202) 885-2275 Fax (202) 885-2224 Michael Milligan <Michael.Milligan @ingenix.com> To Sent by: "'oracle-l@xxxxxxxxxxxxx'" oracle-l-bounce@f <oracle-l@xxxxxxxxxxxxx> reelists.org cc Subject 02/11/2004 06:50 DB links PM Please respond to oracle-l@freelist s.org Has anyone had trouble using a link created for just certain databases, when everything is set right. In other words, no reason can be seen why it wouldn't work? We changed the global name, etc., etc. I can connect to the database but when I create the link, then try to select from dual from another database, it says "TNSNames cannot resolve service name". But using the same service name, I immediately connect successfully to the database! Driving us crazy. Any help would be greatly appreciated. Thanks, Mike This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------