关于Create DataBase Link

bin_huhj

最初由 nygq 发布
[B]我這里客戶端都是用oracle9.0.1的,而服務器端是oracle10的linux系統,這種情況不會有什麼衝突吧! [/B]

這建立db link跟你的客戶端沒什么關系。舉個例子吧﹗
有兩個oracle10g數據庫A和B﹐如果我要在A數據庫上建個dblink JADEA連接B數據庫的HUMAN用戶:
create database link JADEA
connect to HUMAN identified by QPW123
using 'jetoxa';


那么我必須在A數據庫的tnsnames.ora內新增個連接字串﹕jetoxa

nygq

以下是我在SQL*Plus中的執行情況:
SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 6月 16 13:42:17 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


連到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create database link JADEA
  2  connect to HUMAN identified by qpw123
  3  using 'jetoxa';

已建立資料庫連結.

SQL> select * from bill_mtn where rownum = 1;

BILL_KIN MONTHLY              SEQ_NO FL
-------- ---------------- ---------- --
BAH      2005/05                   6 F

SQL> select * from bill_mtn@JADEA;
select * from bill_mtn@JADEA
                                    *
ERROR 在行 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL> drop database link jadea;

已廢棄資料庫連結.

SQL>

bin_huhj

在建立dblink的時候﹐oracle并不會驗証連接字串是否有效。
那你的jetoxa字串是在哪個數據庫上的﹖

下面是我模擬的ORA-12154錯誤(racdb2a根本就不存在)﹕

SQL> conn sys/system@t2.35 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>  create database link logmnr_t
  2  connect to logmnr identified by logmnr
  3  using 'racdb2a';

Database link created

SQL> conn logmnr/logmnr@t2.35
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as logmnr

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ---------
BIN$FYx3kv+POejgQBisJAIklw==$0 TABLE   
STU                            TABLE   
BIN$FccKeIx+RW7gQBisJAJxvw==$0 TABLE   
BIN$FccKeIx9RW7gQBisJAJxvw==$0 TABLE   
BIN$Fdyae0M6s4vgQBisJAJEcQ==$0 TABLE   
TEST2                          TABLE   
BIN$FZsLPP2/U1fgQBisJAJMVQ==$0 TABLE   
BIN$FZsLPP3AU1fgQBisJAJMVQ==$0 TABLE   
TEST3                          TABLE   
TEST4                          TABLE   
BIN$FYjswne4L//gQBisJAIcbA==$0 TABLE   

11 rows selected

SQL> conn sys/system@t2.35 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL> select count(*) from stu@logmnr_t;

select count(*) from stu@logmnr_t

ORA-12154: TNS: 無法解析指定的連線 ID

SQL>

nygq

我的tnsnames.ora文件內容如下:
# TNSNAMES.ORA Network Configuration File: c:\oracle\ora90\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

JETOXB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.8)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = jetoxb)
    )
  )

JETOXA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orc1)
    )
  )

JETOX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.249)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )

JETOXT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orc1)
    )
  )

我的jetoxa是連接在jetox數據庫上的,其中jetox,jetoxa,jetoxb都是orcale 10g,jetoxt是orcale8.0

nygq

如果我在jetoxt上連接到jetoxa就可以,這讓很是想不通,以下是結果:

SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 6月 16 14:26:55 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

連到:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SQL> create database link JADEA
  2  connect to HUMAN identified by qpw123
  3  using 'jetoxa';

已建立資料庫連結.

SQL> select count(*) count from bill_mtn@jadea;

     COUNT
----------
       170

SQL> drop database link jadea;

已廢棄資料庫連結.

SQL>

bin_huhj

最初由 nygq 发布
[B]我的tnsnames.ora文件內容如下:
# TNSNAMES.ORA Network Configuration File: c:\oracle\ora90\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

JETOXB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.8)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = jetoxb)
    )
  )

JETOXA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orc1)
    )
  )

JETOX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.249)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )

JETOXT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orc1)
    )
  )

我的jetoxa是連接在jetox數據庫上的,其中jetox,jetoxa,jetoxb都是orcale 10g,jetoxt是orcale8.0 [/B]

你這個文件是oracle9i的吧﹐我的意思是在建立dblink的那個數據庫上的tnsnames.ora內加入﹕
JETOXA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orc1)
    )
  )
明白我的意思嗎﹖

nygq

在我本機上只有兩個tnsnames.ora文件,分別在:
C:\oracle\ora90\network\ADMIN\tnsnames.ora和C:\oracle\ora90\network\ADMIN\SAMPLE\tnsnames.ora,你所指的是服務器端的tnsnames.ora嗎?在我本機是用net manager配置的

我還是笨了點!

nygq

雖然現在還沒把問題根源找到,但還很感謝bin_huhj的解惑!