Re: Problem with CREATE DATABASE LINK ...

In article <DH0E8y.EFI_at_mecati.mecasoft.ch>, unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) writes:
> Hi folks,
>

 [..........]
>
> I have then logged in the dbadm database as user 'auditor' and
> defined two database links:
> --------------------------------------------------------------
> aud> create database link test7
> 2 connect to system identified by ... using 'test7';
>
> aud> create database link prod7
> 2 connect to system identified by ... using 'prod7';
>
>
> And, surprise surprise, the FIRST works, the second NOT !!
> ----------------------------------------------------------
> aud> select * from t01_at_prod7;
> ORA-02085: database link PROD7.WORLD connects to PROD7.MURALTOCH
>
> This will works with:
> ---------------------------------------------------------
> aud> create database link prod7.muraltoCH
> 2 connect to system identified by ... using 'prod7';
> aud> select * from t01_at_prod7.muraltoCH;
>
> But I don't like to add my domain, and why one works and the other not???
>
> I have also tryied to add the domain at the first one:
> --------------------------------------------------------------------
> aud> select * from t01_at_test7.muraltoCH;
> ORA-02019: connection description for remote database not found
>
> aud> create database link test7.muraltoCH
> 2 connect to system identified by ... using 'test7';
> aud> select * from t01_at_test7.muraltoCH;
> ORA-02085: database link TEST7.MURALTOCH connects to TEST7.WORLD
>
>
> Very strange, isn't it ????? Any suggestion welcomed!
> Stefano
>
> Stefano Unternaehrer Mecasoft SA
> Oracle DBA 6600 Muralto
> PL/SQL, Pro*C, C, XWindow & Motif Switzerland Europe
> Software Developer fax: +41 91 743 5507
> email: dba,unter_at_mecasoft.ch voice: +41 91 743 7444
>

Stefano,

I think that the reason why it's working like that is that you have turn global naming on (global_names = true in your init.ora file) and you have the db_domain parameter set to two different values ('world' in your test7 database and 'murltoch' in your prod7 database). To find out exactly what is the domain name that you are using you can execute the following query on each of your database:

SQL> select * from global_name;

Check also your manuals to understand why it's working this way!

-- 
------------------------------------------------------------------------------
Pierre Hollard                                 
Oracle Database Administrator                  Phone: (404) 827-0656
Turner Broadcasting System, Inc.              E-Mail: phollard_at_dev1.turner.com
------------------------------------------------------------------------------