GLOBAL NAME summary

I have always wrongly understood this stuff with global names. Even if dig something up, shortly after the use I forget. So here is a summary.

GLOBAL NAME definition

It is fully taken from the Oracle documentation (Understanding How Global Database Names Are Formed).

A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:

• DB_NAME
• DB_DOMAIN

The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup. The place, where the GLOBAL NAME is stored is the table SYS.PROP$. You may also display it with a call to the view called GLOBAL_NAME (there is also a global synonym to this view with the same name).
One more note - in commands specific for db link name (like CREATE DATABASE LINK, ALTER DATABASE RENAME GLOBAL_NAME, etc. where the db link name is used as identifier and not as the value one should use double quotes. However surprisingly a little the name is case-insensitive i.e. one may use "ORCL.WORLD@ANOTHER_USER" as well as "orcl.WORLD@ANOTHER_USER" - the call will work

SQL> SELECT * FROM props$ WHERE name='GLOBAL_DB_NAME';

NAME            VALUE$   COMMENT$
--------------- -------- --------------------
GLOBAL_DB_NAME  EXAMPLE  Global database name

SQL> SELECT * FROM global_name;

GLOBAL_NAME
---------------------------------------------
EXAMPLE

GLOBAL NAME change

I spotted 2 ways to achieve it (DbForums and others):

1. ALTER DATABASE RENAME GLOBAL_NAME TO "[new global name]";

   IIRC this way has some limitations - e.g. if domain was once defined, it always will be added, thus no way to remove it completely
2. because this parameter is stored in a table, it is possible to update it with simple SQL

   UPDATE sys.prop$ SET value$='[new global name]' WHERE name='GLOBAL_DB_NAME';
   -- update on view also works IIRC
   -- UPDATE global_name SET global_name='[new global name]';
   COMMIT;
   

   This way we may set the GLOBAL NAME to anything we want. However this is messing with the db dictionary, so a little frightening and there is a Metalink note id 1018063.102, when the change hangs. In short when one once starts to update the GLOBAL NAME manually, have to do it this way further.

GLOBAL_NAMES parameter

The GLOBAL_NAMES parameter enforces naming db links as GLOBAL NAME of source databases. The parameter is of Boolean type and may be set globally or in a session.

With the GLOBAL_NAMES enabled one may create many connections to the same database (for example differing in credentials; AskTom.oracle.com) by using @ sign, e.g.

-- most probably You need here double quotes around the db link name
-- at least I have got ORA-00933: SQL command not properly ended if not use them
create database link "ORCL.WORLD@SCOTT_USER"
connect to scott identified by tiger
using 'orcl.world';

create database link "ORCL.WORLD@ANOTHER_USER"
connect to another identified by lion
using 'orcl.world';

select 1 from dual@"ORCL.WORLD@ANOTHER_USER";