ORACLE資料庫中Global Database Name與DB LINKS的關係還真是有點糾纏不清，在說清楚這個關係前，我們先來瞭解一下Global Database Name的概念 Global DataBase Name 概念 1. What is a global database nam... ...
ORACLE資料庫中Global Database Name與DB LINKS的關係還真是有點糾纏不清，在說清楚這個關係前，我們先來瞭解一下Global Database Name的概念
Global DataBase Name 概念
1. What is a global database name?
-------------------------------------------------------------------------------
The global database name is the unique name of the database. In a distributed
database system (a set of databases stored on multiple computers that typically
appears to applications as a single database) the global database name ensures
that each database is distinct from all other databases in the system. Oracle
forms a database's global database name by prefixing the database's network
domain with the individual database's name. For example: sales.us.oracle.com
and sales.uk.oracle.com.
The global database name defaults to DB_NAME.DB_DOMAIN and this value is marked
at database creation time. If you change the DB_NAME or DB_DOMAIN after the
database has been created, the value for the global database name (GLOBAL_NAME)
will not change.
說簡單一點，global database name就是用來唯一標識資料庫的東東。global database name由兩部分組成，DB_NAME和DB_DOMAIN。在創建db link的時候，Oracle會自動將db_domain作為尾碼添加上去。而且一旦加入就很難變更。所以在進行高級複製、Streams複製等配置時，最好首先將多個節點的global_name規劃好。
如何查看Global Database Name
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------TEST.REGRESS.RDBMS.DEV.US.ORACLE.COMSQL>
如下所示，可以知道global_name為一個視圖。
SQL> select object_name,object_type from dba_objects where object_name=upper('global_name');
OBJECT_NAME OBJECT_TYPE
---------------------------------------- -------------------------------GLOBAL_NAME VIEWGLOBAL_NAME SYNONYM
視圖global_name的定義可以從DBA_VIEWS裡面查看，如下所示，它來源於sys.props$內部表
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'
那麼參數global_name與Global Database Name又沒有區別呢？ 其實參數global_name僅僅表示指定資料庫的鏈接是否需要和它所連接的資料庫相同的名稱。
GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.
SQL> show parameter global_name
NAME TYPE VALUE---------------------------------- ----------- -----------------global_names boolean FALSESQL>
所以兩者完全是兩個不同的概念，但是global_name也很重要，因為它的值會影響DB LINK.接下來，我們來創建一個DB Link，如下所示
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------KKK.REGRESS.RDBMS.DEV.US.ORACLE.COMSQL> CREATE PUBLIC DATABASE LINK "TEST"
CONNECT TO "test" IDENTIFIED BY test1111
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.27.109)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = QWR)))';
Database link created.SQL>
如上所示，我創建的DB Link名字為TEST，但是你查詢DBA_DB_LINKS時，你會發現鏈接伺服器自動加上了功能變數名稱，變成了TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM, 當你查詢時，使用TEST或TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM都OK。
SQL> SELECT * FROM DUAL@TEST;
D
-
X
SQL> SELECT * FROM [email protected];
D
-
X
SQL>
當Global Database Name只有DB_NAME,但是沒有DB_DOMAIN時，此時，如果創建的DB Link是沒有db_domain作為尾碼添加上去的，你查詢時，就必須使用全名TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM，不能像上面那樣使用TEST，否則會報ORA-02019: connection description for remote database not found 錯誤。
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------EPPSSQL> CREATE PUBLIC DATABASE LINK "TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM"
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.27.109)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = QWR)))';
Database link created.SQL> SELECT * FROM DUAL@TEST;
SELECT * FROM DUAL@TEST
*
ERROR at line 1:ORA-02019: connection description for remote database not found
SQL> SELECT * FROM [email protected];
D
-
X
SQL>
DB_LINK與GLOBAL_NAMES參數的關係
當GLOBAL_NAMES為TURE時，影響的是創建資料庫鏈接的那個庫對資料庫鏈接的使用。而不是鏈接到該資料庫的鏈接伺服器的使用。也就是說，如果一個庫（實例）的global_names參數設值為TRUE，則該庫連接其他庫的資料庫鏈接，其名稱必須要與被連接的庫的global_name相同，是否有點繞口，那麼從下麵實驗看看。
伺服器B
SQL> show parameter global_names;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------global_names boolean FALSESQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
KKK.REGRESS.RDBMS.DEV.US.ORACLE.COM
伺服器A
SQL> show parameter global_name;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------global_names boolean FALSESQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SCM2.REGRESS.RDBMS.DEV.US.ORACLE.COM
在伺服器B上創建鏈接伺服器，鏈接到伺服器A
SQL> CREATE PUBLIC DATABASE LINK "LINK1"
2 CONNECT TO "test" IDENTIFIED BY test1111
3 USING 'SERVER_A';
Database link created.SQL> SELECT * FROM DUAL@LINK1;
