假設有2台oracle數據庫，一台叫test，一台叫prod，都是9204。
其中test在window xp平台，prod在RHEL AS3。
test:
db_name=dba
db_domain=world
glabal_name=dba.world
prod:
db_name=ora9i
db_domain=oracle.com
global_name=ora9i.oracle.com
db link指向是: test -> prod
假設test和prod數據庫上都有scott和mhung用戶。
test上指向prod的網絡連接串是as3。
db link 有3種類型，我這裡只討論其中兩種，connected user和fixed user。
connected user，簡單來說，test數據庫以connected的用戶來連接遠程數據庫(prod)。
fixed user,簡單來說，test數據庫以fixed(指定的)用戶來連接遠程數據庫(prod)。
看看創建db link語法，你對這兩種類型就比較清楚了。
創建fixed user的db link語法：
create databas link foo connect to scott identified by tiger using 'as3' ;
創建fixed user的db link語法：
create databas link foo using 'as3' ;
db link 的命名和global_names有關，
如果global_names=true(test),那麼db link的命名要和遠程數據庫(prod)的global_name相同；
如果global_names=false(test),那麼你可以隨便命名db link。
請注意，是test數據庫的global_names,與prod數據庫的global_names無關。
global_name是數據庫全局名稱，global_name在你所管理的數據庫中要保證唯一。
數據庫名稱是db_name。數據庫名稱一般都取得比較短，我的習慣一般取長度4個字符，重名概率高。
比如你有2個數據庫的db_name都是prod，重名啦。
所以，oracle模仿域名搞出一個global_name,global_name=db_name+db_domain。
有了global_name,就可以實現數據庫命名的全局唯一。
4種情況如下表。
global_names（test數據庫） db link type
----------------- -------------
1. false connected user
2. false fixed user
3. true connected user
4. true fixed user
1. global_names=false, link type=connected user
[email protected]> create database link foo using 'as3' ;
Database link created.
Elapsed: 00:00:00.00
[email protected]> select * from global_name ;
GLOBAL_NAME
--------------------------------------------------------
DBA.WORLD
Elapsed: 00:00:00.00
[email protected]> select * from global_name@foo ;
GLOBAL_NAME
--------------------------------------------------------
ORA9I.ORACLE.COM
Elapsed: 00:00:00.04
[email protected]>
2. global_names=false, link type=fixed user
[email protected]> create database link woo connect to mhung identified by huang using 'as3' ;
Database link created.
Elapsed: 00:00:00.00
[email protected]> select * from global_name@woo ;
GLOBAL_NAME
----------------------------------------------------------------------------------------------
ORA9I.ORACLE.COM
Elapsed: 00:00:00.04
[email protected]> select *from global_name ;
GLOBAL_NAME
----------------------------------------------------------------------------------------------
DBA.WORLD
Elapsed: 00:00:00.00
[email protected]>
以下測試global_names=true的情況，
3. global_name=true, link type=connected user
[email protected]> show parameter global
NAME TYPE VALUE
------------------------------------ ----------- ----------------
global_context_pool_size string
global_names boolean FALSE
[email protected]> alter system set global_names=true ;
System altered.
Elapsed: 00:00:00.01
[email protected]>
[email protected]> select * from global_name@foo ;
select * from global_name@foo
*
ERROR at line 1:
ORA-02085: database link FOO.WORLD connects to ORA9I.ORACLE.COM
Elapsed: 00:00:00.00
[email protected]>
看看，global_names=true後，剛才好好的foo,馬上失效了。
現在我把global_names改為false再看看。
[email protected]> alter system set global_names=false ;
System altered.
Elapsed: 00:00:00.01
[email protected]> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ----------
global_names boolean FALSE
[email protected]>
[email protected]> select * from global_name@foo ;
GLOBAL_NAME
----------------------------------------------------
ORA9I.ORACLE.COM
Elapsed: 00:00:00.00
[email protected]>
你看，foo又好了。
廢話一堆，現在開始第3種情況測試，首先把global_names改為true。
[email protected]> alter system set global_names=true ;
System altered.
Elapsed: 00:00:00.01
[email protected]> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------
global_names boolean TRUE
[email protected]>
開始創建db link。
[email protected]> create database link ora9i.oracle.com using 'as3' ;
Database link created.
Elapsed: 00:00:00.00
[email protected]> select * from [email protected] ;
GLOBAL_NAME
--------------------------------------------------------------------
ORA9I.ORACLE.COM
Elapsed: 00:00:00.05
[email protected]>
4. global_name=true, link type=fixed user
（略）。
db link是單向的。
在 test 上創建 test->prod 的 db link：
D:\oraclass\sql>sqlplus system/manager ### 以system登錄
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Sep 2 10:24:44 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[email protected]> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
[email protected]> select * from global_name ;
GLOBAL_NAME
-------------------------------------------------------------------------------
DBA.WORLD
Elapsed: 00:00:00.00
### 請注意global_names和global_name的區別，呵呵。
[email protected]> create public database link ora9i.oracle.com using 'as3' ;
Database link created.
Elapsed: 00:00:00.01
### 創建db link，as3是test->prod的網絡連接串
[email protected]> @c mhung/huang 以mhung登錄
[email protected]>
[email protected]> select * from [email protected] ;### 遠程數據庫
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
T TABLE
T_A SYNONYM
Elapsed: 00:00:10.04
[email protected]> select * from tab ;### 本地數據庫
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP30 TABLE
INT_EMP30 TABLE
T TABLE
Elapsed: 00:00:00.00
[email protected]> select * from t ; ### 請注意本地表t的內容
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
Elapsed: 00:00:00.00
[email protected]>
在 prod 上創建 prod->test 的 db link：
[oracle@O9i sql]$ sqlplus system/manager ### 以system登錄
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Sep 2 10:28:30 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[email protected]> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
[email protected]> select * from global_name ;
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
ORA9I.ORACLE.COM
Elapsed: 00:00:00.03
[email protected]>
[email protected]>
[email protected]> create public database link dba.world using 'dba' ;
Database link created.
Elapsed: 00:00:00.03
### 創建db link，dba是prod->test的網絡連接串
[email protected]> @c mhung/huang ### 以mhung登錄
[email protected]>
[email protected]> select * from [email protected] ; ### 遠程數據庫
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP30 TABLE
INT_EMP30 TABLE
T TABLE
Elapsed: 00:00:00.15
[email protected]> select * from tab ; ### 本地數據庫
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
T TABLE
Elapsed: 00:00:00.01
[email protected]> select * from t ; ### 請注意本地表t的內容
C
--------------------
福州
begin 北伐軍 ; end;
沙蝶
Elapsed: 00:00:00.01
[email protected]>
幾個demo操作：
1. prod上mhung用戶創建synonym,指向[email protected]
[email protected]> create synonym t_test for [email protected] ;
Synonym created.
Elapsed: 00:00:00.10
[email protected]> select * from t_test ;
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
Elapsed: 00:00:00.04
[email protected]>
2. test上mhung用戶，插入數據到 [email protected] 中。
[email protected]> select * from [email protected] ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
T TABLE
T_TEST SYNONYM
Elapsed: 00:00:00.00
[email protected]> select * from [email protected] ;
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
Elapsed: 00:00:00.04
[email protected]> select *from t ;
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
Elapsed: 00:00:00.00
[email protected]> insert into [email protected] values(5,'插本地') ;
1 row created.
Elapsed: 00:00:00.00
[email protected]> commit;
Commit complete.
Elapsed: 00:00:00.00
[email protected]> select * from [email protected] ;
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
5 插本地
Elapsed: 00:00:00.00
[email protected]> select *from t ;
A B
---------- ----------
1 燚
2 燚燚已以
3 1燚燚已以
4 數據庫
5 插本地
Elapsed: 00:00:00.00
[email protected]>
### [email protected]是什麼，就是test上mhung用戶的t表。
繞了一圈，實際上就是本地操作，呵呵。
