A detailed study of Oracle database Global_names parameters (useful when creating Dblink)

The knowledge of Oracle database Global_names parameters is the main content of this article, when using Database link when the Global_names parameter is set to True, the database Link must have the same name as the global_name of the connected library. Here is a test, in which the library creating the database link is XJ (WINDOWS 2003 Oracle 10g 10.2.0.1) and the linked library is Dmdb (LINUX AS5 Oracle 10g 10.2.0.1 RAC).

First look at the relevant configuration of Dmdb:

Here is the code snippet:

Sql> Show Parameter Global_names
NAME TYPE VALUE
------------------------------------ ----------- ---------
Global_names Boolean FALSE
Sql> select * from Global_name;
Global_name
----------------------------------------------------------
Dmdb
You can see this linked library whose global_names parameter is false.

To create a database-linked library configuration:

Here is the code snippet:

Sql> Show Parameter Global_names
NAME TYPE VALUE
------------------------------------ ----------- -------
Global_names Boolean FALSE
Sql> select * from Global_name;
Global_name
--------------------------------------------------------
Xj
Then do the following:

Here is the code snippet:

Sql> CREATE DATABASE link Test_link connect to test identified by test using ' dmdb ';
The database link was created.
Sql> SELECT * from [email protected]_link;
D
-
X

You can see that the database link works correctly.

Set Global_names to True on the Dmdb library:

Sql> alter system set global_names=true;

The system has changed.

Query again on the XJ library and create a new database link to query:

Here is the code snippet:

Sql> SELECT * from [email protected]_link;
D
-
X
Sql> CREATE DATABASE link TEST_LINK2 connect to test identified by test using ' dmdb ';
The database link was created.
Sql> SELECT * from [email protected]_link2;
D
-
X
You can see that the database link is working correctly at this point. We will then set the XJ Library's global_names parameter to true:

Here is the code snippet:

Sql> alter system set global_names=true; The
system has changed.
Sql> SELECT * from [Email protected]_link2;
Select * FROM [email protected]_link2
* 1th line error:
ORA-02085: Database link test_link2 connection to Dmdb
Sql> Select * Email protected]_link
Select * FROM [email protected]_link
* 1th line error:
ORA-02085: Database link TES T_link Connect to Dmdb
and set the XJ library's global_names again to false, the database link is available again.
Sql> alter system set GLOBAL_NAMES=FALSE; The
system has changed.
Sql> SELECT * from [Email protected]_link;
D
-
X
sql> select * from [Email protected]_link2;
D
-
X
again sets the global_names of the Dmdb library to False, and the database link is still available:

Here is the code snippet:
On the Dmdb library:
Sql> alter system set GLOBAL_NAMES=FALSE;
The system has changed.
On the XJ library:
Sql> SELECT * from [email protected]_link;
D
-
X
As you can see, links can still be used. If you create a link to the XJ library on the Dmdb library, you can observe the same results. One can conclude that the Global_names parameter is set to False, which affects the use of database links by the library that created the database link. That is, if the Global_names parameter of one library (instance) is set to True, the library connects to the database link for the other library, and its name must be the same as the global_name of the connected library:

Here is the code snippet:

On the XJ library:
Sql> alter system set global_names=true;
The system has changed.
Sql> CREATE DATABASE link Dmdb connect to test identified by test using ' dmdb ';
The database link was created.
Sql> SELECT * from [email protected];
D
-
X
Sql> SELECT * from [email protected]_link;
SELECT * FROM [email protected]_link
*
An error occurred on line 1th:
ORA-02085: Database link Test_link connected to Dmdb
Sql> SELECT * from [email protected]_link2;
SELECT * FROM [email protected]_link2
* Error on line 1th:
ORA-02085: Database link test_link2 connected to Dmdb
What if you want to build multiple databases to link to the same library if Global_names is set to true? Because the name of the database link must be the same as the global_name of the target library. You can do this in the following ways:

Here is the code snippet:

sql> CREATE DATABASE [email protected] to test identified by test using ' dmdb ';
The database link was created.
sql> CREATE DATABASE [email protected] to test identified by test using ' dmdb ';
The database link was created.
sql> SELECT * [email protected];
D
-
X
sql> SELECT * [email protected] @link1;
D
-
X
sql> SELECT * [email protected] @link2;
D
-
X

That is, add @ plus an identity after global_name. This enables you to create multiple databases that are linked to the same target library.

In addition, when creating a database link, you cannot create a link to another schema because the database link has a name that can contain '. ' That is, the dot. For example, a user wants to create a dblink named Linkb,create database link b.linkb for B users ..., this statement will actually create a DB link named b.linkb under a user.

A detailed study of Oracle database Global_names parameters (useful when creating Dblink)