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

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


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
Advertisements
Categories: Miscellaneous Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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

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 !!!!

%d bloggers like this: