Tip#21 DB Link Name Suffix

Recently came across installations where all DB link were having suffix US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM and was bit annoying for users so here is how we went forward to fix it.

As per documentation , whenever we create DB link, the db_domain is automatically appended to it. So following SQL should help determines naming of all DB links,


select name,value from v_$parameter where name IN ('db_name', 'db_domain');

db_name = MYDB
db_domain = NULL

So in our case db_domain was NULL then where does US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM come form, lets check the global name, in case you dont know, GLOBAL_NAME = db_name.db_domain

select global_name from global_name;

global_name = MYDB.US.ORACLE.COM

hmm so global name does show me domain name now, how do I fix it?

Option 1 :

Rename global name with desired domain name e.g. MYWORLD

alter database rename global_name to MYDB.MYWORLD;

Please note with above alter statement, you MUST specify db_domain else it wont work.

Option 2 :

In case you dont wont any domain, Update global name to db_name without db_domain

UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'MYDB';
commit;


Now cross check the changes,

select global_name from global_name ;


global_name = MYDB

Now you can go and create DB link as any user and it will be as expected i.e. with desired suffix or without.

While we are on DB link topic, please note global_name <> global_names i.e.

Global Name is global name of the DB determined by db_name.db_domain

Global Names is a parameter which determines whether DB link should always be named AFTER the global name of the database it connects to. You can set parameter value to be TRUE or FALSE (default) . If you have a replication environment then it is in general a good thing to set it to true.