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
Also view
http://hoopercharles.wordpress.com/2010/01/27/neat-tricks/
Anand
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
Hi,
What happens after setting
alter session set global_names=false
select * from dual@seresdb
Anand
Create a public synonym for the db link and it should work without any issues.