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 scott@oracle to scott@brave.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/tiger@oracle
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 dual@scott_dblink;
select * from dual@scott_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 dual@brave;

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 dual@test;
select * from dual@test
 *
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 scott@brave to scott@oracle 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/tiger@brave
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 dual@test;

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 dual@oracle;

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

About these ads

4 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 dual@brave;
    select * from dual@seresdb
    *
    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s