Mamun's DBA Blog: DB Link and GLOBAL_NAMES parameter in Oracle

GLOBAL_NAMES is either set to FALSE OR TRUE.

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

If the value of GLOBAL_NAMES is FALSE, then any name can be given to DB_LINK. If value is TRUE, then database link name should be same as that of the database it is pointing.

Default value is FALSE:

SQL> show parameter global_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     FALSE

1. Lets create a database link with any name:

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@TESTDB;

SYSDATE

---------

01-MAR-19

SQL>drop public database link TESTDB ;

So with GLOBAL_NAME set to FALSE, db_link name is independent of target database name.

2. Update the GLOBAL_NAMES value to TRUE:

SQL> alter system set global_names=TRUE scope=both;

System altered.

SQL> show parameter global_names

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------------------------

global_names                         boolean     TRUE

3. Create database link as before(with different name)

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created

SQL> select sysdate from dual@TESTDB;

select sysdate from dual@TESTDB

                         *

ERROR at line 1:

ORA-02085: database link TESTDB connects to ORCL

We can see, when global_name is TRUE, DB_LINKs with different name as that of database name is not working.

4. Lets create a db_link having same name as target db.

SQL> create public database link ORCLconnect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@ORCL;

SYSDATE

---------

01-MAR-19

Hope it Works!!!