Archive

Posts Tagged ‘global_names’

How to create multiple db links with global_names set as ‘TRUE”

April 2, 2013 Leave a comment

Setting GLOBAL_NAMES=TRUE => To enforce that a dblink has same name as the GLOBAL_NAME of the DB it connects to.

Lets test this with an example,

DB_NAME DB_DOMAIN GLOBAL_NAME GLOBAL_NAMES
bharat_test world bharat_test.world FALSE
prod —– prod TRUE

As seen in the table, we have set GLOBAL_NAMES=TRUE in the prod database, so any db link from prod to other database should be created as global_name of that database.

Here db link in “prod” should be created as “bharat_test.world”

To check the database global name

SQL> select global_name from global_name;

GLOBAL_NAME
---------------------------
bharat_test.world

To rename the database global name,

Alter database rename GLOBAL_NAME to <<new global_name>>;

Here we will create 2 db links in ‘prod’ database and will test them,

SQL> create database link will_fail connect to dbaoper 
                            identified by password using 'bharat_test';
Database link created.
SQL> create database link bharat_test.world  connect to dbaoper 
                            identified by password using 'bharat_test';
Database link created.

Now test these links,

SQL> select sysdate from dual@will_fail;
 select sysdate from dual@will_fail
 *
 ERROR at line 1:
 ORA-02085: database link WILL_FAIL.WORLD connects to NAGITEST.WORLD
SQL> select sysdate from dual@bharat_test.world
SYSDATE
 ---------
 02-APR-13
 << THE RESULT IS SUCCESS>>

Here we could see that setting GLOBAL_NAMES=TRUE limits the db link name to that of the target db global name. So the big question is

“How Do We Create Multiple DB Links In This Scenario?”

The answer would be “leave global_names on but use @ in the dblink name. Put the global name first in the dblink name and then extra stuff after an @”

Example:

SQL> create database link bharat_test.world@new_db_link connect to
                   dbaoper identified by password using 'bharat_test';
Database link created.
SQL> select sysdate from dual@bharat_test.world@new_db_link;
SYSDATE
---------
02-APR-13
Categories: Miscellaneous Tags: ,
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!