Monday, May 19, 2014

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>

No comments: