Allen

建立 database links：

If you have same user with same password on both databases (9i and 10G), then you can create a public database link as

CREATE PUBLIC DATABASE LINK dblink USING 'dbalias';

Note: dbalias is the value from your TNSNAMES.ORA. The database from which you are creating the db link has to have the entry in TNS for other database.

Other method is to create a private db link

CREATE DATABASE LINK dblink CONNECT TO user IDENTIFIED by pwd using 'dbalias';

建立新user設定select權限及可select的table：

(1) --建立一個user名稱HCCG (CREATE USER HCCG)密碼預設為 HC (語法:IDENTIFIED BY HC)
      CREATE USER HCCG IDENTIFIED BY HC ;
(2) --賦予HCCG可以連接資料庫的權限
      GRANT CREATE SESSION TO HCCG;

(3) --將TITAN下面的table:LCN建立成為公用synonyms:LCN_V

      create public synonym LCN_V for TITAN. LCN;
(4) --賦予user:HCCG可以讀取(select)TITAN裡面的table:LCN的權限
      grant select on LCN to HCCG;

(5) --更換USER HCCG，測試是否可select 設定的table

select * from LCN_V ;

目前測試oracle 9i 與 10g兩個版本相互做database links的方式如下：

•  近端用oracle 9i在using的部分要先在OEM上的Database建立一個連線，確定可以連線後再使用該設定的名稱(tns_service_name)做Database Links，如OEM上沒有建立，Database Links 也無法連線。OEM的設定也可以直接修改檔案oracle home/network/admin/tnsnames.ora。

HCTS_192.168.1.55 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.55)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = HCTS)

      (SERVER = DEDICATED)

    )

  )

        以上面的tnsnames.ora內的設定為例子

tns_service_name = HCTS_192.168.1.55

SID = HCTS

• 近端用oracle 10g 在using的部分輸入『//主機名稱或IP:PORT/DB_SID』，OEM上的Database裡沒有建立連線，在Database links還是可連線。