Global_Names and ORA-02085

This was tested on Oracle 9. I created 2 database links to the same target database:

 

SQL> conn / as sysdba

Connected.

SQL> create database link any_name.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL> create database link remotedb.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL>

 

I checked the value of the source database’s global_names parameter:

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

FALSE

 

SQL>

 

I tested both links and they worked OK:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

I changed global_names to true in the source database:

 

SQL> alter system set global_names = true

  2  /

 

System altered.

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

TRUE

 

SQL>

 

This stopped the any_name.world link working as the link name in the source database and the global_name of the target database must match if global_names is set to true in the source database:

 

SQL> select sysdate from dual@any_name.world

  2  /

select sysdate from dual@any_name.world

                         *

ERROR at line 1:

ORA-02085: database link ANY_NAME.WORLD connects to

REMOTEDB.WORLD

 

SQL>

 

But the remotedb.world link still worked:

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

How do I know that the check is made against the target database’s global name? I connected to the target database and changed its global name to any_name.world:

 

SQL> conn /@remotedb.world

Connected.

SQL> alter database rename global_name

  2  to any_name.world

  3  /

 

Database altered.

 

I reconnected to the source database: 

SQL> conn / as sysdba

Connected.

SQL>

 

Then the any_name.world link worked:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

But the remotedb.world link didn’t:

 

SQL> select sysdate from dual@remotedb.world

  2  /

select sysdate from dual@remotedb.world

                         *

ERROR at line 1:

ORA-02085: database link REMOTEDB.WORLD connects to

ANY_NAME.WORLD

 

SQL>