Oracle

DB Link Name and DB Global Name

The database link name do have dependencies on the global database name.Global database names uniquely identify a database in the system.It is formed from two components: a database name and a domain.The database name and the domain can be determined by the following initialization parametrs:-
For database name  :-  db_name

22:34:00 SYS @ oracle >show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oracle

For Domain :-  db_domain

22:34:03 SYS @ oracle >show parameter db_domain
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string

As per Oracle document
“The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database global database name as the name of the link.”
To determine whether global naming on a database is enforced on a database, either examine the database initialization parameter file or query the V$PARAMETER view.

23:06:37 SYS @ oracle >SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names';
NAME                 VALUE
-------------------- ----------
global_names         TRUE
23:08:13 SYS @ oracle >show parameter global_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

I too had thought the same  “The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming.”
But i am in a little confused state now,after the below scenario:-
I have 2 databases , namely oracle and brave.On Oracle the global_name parameter is “TRUE” whereas on brave database its “FALSE”.

Session 1:-

23:15:08 SYS @ oracle >select name from v$database;
NAME
--------------------
ORACLE
23:15:15 SYS @ oracle >show parameter global_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE
23:18:08 SYS @ oracle >select * from global_name;
GLOBAL_NAME
-------------------------------------------------------
ORACLE
Elapsed: 00:00:00.26

Session 2:-

23:16:06 SYS @ BRAVE >select name from v$database;
NAME
---------------
BRAVE
23:16:12 SYS @ BRAVE >show parameter global_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE
23:16:43 SYS @ BRAVE >select * from global_name;
GLOBAL_NAME
------------------------------------------------
BRAVE

Now i create a database link from [email protected] to [email protected].As the remote database has global_name parameter false i must be able to create and use the database link with any name as per the oracle document and my understanding.Its not necessary to have the database link name same as the global database name of the remote database.

Session 1:-

23:20:27 SYS @ oracle >grant create database link to scott;
Grant succeeded.
23:20:36 SYS @ oracle >conn scott/[email protected]
Connected.
23:20:46 SCOTT @ oracle >create database link scott_dblink connect to scott identified by tiger using 'BRAVE';
Database link created.
23:20:49 SCOTT @ oracle >
23:20:50 SCOTT @ oracle >
23:20:50 SCOTT @ oracle >select * from [email protected]_dblink;
select * from [email protected]_dblink
 *
ERROR at line 1:
ORA-02085: database link SCOTT_DBLINK connects to BRAVE
23:21:08 SCOTT @ oracle >create database link brave connect to scott identified by tiger using 'BRAVE';
Database link created.
23:21:31 SCOTT @ oracle >select * from [email protected];
D
-
X
23:21:37 SCOTT @ oracle >create database link test  connect to scott identified by tiger using 'BRAVE';
Database link created.
23:21:48 SCOTT @ oracle >select * from [email protected];
select * from [email protected]
 *
ERROR at line 1:
ORA-02085: database link TEST connects to BRAVE

Why am i forced to use the remote database global name when the global_name parameter is set to “FALSE” on the remote database??
Now, lets try to create a database link from the [email protected] to [email protected] and see.Remember, the global_name parameter in Oracle is set to “TRUE”.

Session 2:-

23:22:18 SYS @ BRAVE >grant create database link to scott;
Grant succeeded.
23:22:27 SYS @ BRAVE >conn scott/[email protected]
Connected.
23:22:29 SCOTT @ brave >create database link test connect to scott identified by tiger using 'ORACLE';
Database link created.
23:22:37 SCOTT @ brave >select * from [email protected];
D
-
X
23:23:34 SCOTT @ brave >create database link oracle connect to scott identified by tiger using 'ORACLE';
Database link created.
23:23:53 SCOTT @ brave >select * from [email protected];
D
-
X

I am able to create the database link with any name and use it, though the global_name parameter is set to true in remote database.Has the oracle document statement need to be changed to
“The name that you give to a link on the local database depends on whether the local database enforces global naming. If the local database enforces global naming, then you must use the remote database global database name as the name of the link.”
OR
Am i going wrong somewhere?Please let me know.
NOTE :- The database version i am using is 10.2.0.4

6 thoughts on “DB Link Name and DB Global Name

  1. I’m facing the same problem, but in the Global domain enabled database I actually have a domain name.
    In this case, I’m unable to create any kind of dblink, because since the local DB has a domain name, when creating the dblink without any domain name Oracle appeds the local domain name, and then it’s unable to connect because the remote DB has no domain name at all.
    This example following your namings:
    DB “oracle”:
    – Global naming : true
    – select * from global_name : oracle.mydomain.com
    DB “brave”:
    – Global naming: false
    – select * from global_name : brave
    now, in “oracle” DB:
    SQL> create database link brave connect to scott identified by tiger using ‘brave’;
    Database link created.
    SQL> select db_link from user_db_links;
    DB_LINK
    ——————-
    BRAVE.MYDOMAIN.COM
    SQL> select * from [email protected];
    select * from [email protected]
    *
    ERROR in line 1:
    ORA-02085: Database link BRAVE.MYDOMAIN.COM connects to BRAVE
    —-
    I’ve tryed quoting, single, double, etc.. and nothing. There is no way I can create this DBLINK?
    My DB versions 11.2.0.2

Leave a Reply