Re: Query on database link

From: <Nam_Phan_at_manulife.com>
Date: Mon, 9 Oct 2000 16:46:17 +0700
Message-Id: <10644.118768@fatcity.com>

Hi,
Pls. check your current system to see whether it meets the following conditions or not:
1. You must have CREATE SESSION privilege on the remote Oracle database. Net8 must be installed on both the local and remote Oracle databases. 2. To use the link, the current user must be a global user with global accounts on both the local and remote databases. Both databases must be members of the same security domain.

And then I'll give you an example to create database link. I have two servers in the same Domain: UTEST1 and UTEST2. In UTEST1, my connect string is DEV1 and user name's TEST1. In UTEST2, my connect string is DEV2 and user name's TEST2. -Using SQL*PLUS to log on DEV1 by TEST1 user and create new table, for example, employee.
-Using SQL*PLUS to log on DEV2 by TEST2 user and then create database link as follows:

     create public database link test1.dev1 connect to test1 indentified by test1

     using 'dev1';
     commit;

After finishing, you can select data from employee as follows: select * from employee_at_test1.dev1;

Everything will be fine. For further information, do not hesitate to contact me.

Hopefully you get success,

Phan Hoai Nam
Business Analyst,
Chinfon Manulife Insurance Co., Ltd.
Tel: 84-8-8257722, Ext: 2903
Mobile: 091-74 86 95

"magesh" <magesh_at_garoo.ne.jp>@fatcity.com on 10/09/2000 01:11:03 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
Subject: Query on database link

Dear All

 I? created a database link between 2 database . When i am try to access the table "emp" of scott scheme from the second database , getting an error like this ...

sql> select * from emp_at_orcl.kaiji1;

Error at line 1:
 ora-02085 :database links orcl.kaiji1 connects to orcl.world.

Why itz trying to connect to "orcl.world" instead of?"orcl.kaiji1", How to resolve this problem ?

Details of 2 databases :-

first  database(Source)?-----------------------------------Second

database(destination)

db_name :  ora8------------------------------------------------orcl
global_name:true----------------------------------------------true
db_domain:none-----------------------------------------------kaiji1

Listener.ora of ORCL

---

SID_LIST_LISTENER =

?(SID_LIST

???(SID_DESC =

????? (GLOBAL_DBNAME= ORCL.kaiji1)
?????(ORACLE_HOME=  /u01/app/oracle/product/8.0.5)
?????(SID_NAME =  ORCL)
???)

tnsnames.ora of ORCL

---

ORCL.kaiji1 =

?(DESCRIPTION

???(ADDRESS = (PROTOCOL= TCP)(Host= kaiji1)(Port= 1521))
???(CONNECT_DATA = (SID = ORCL))

? )

tnsnames.ora of?ora8

---

kaiji1=

?(DESCRIPTION

???(ADDRESS = (PROTOCOL= TCP)(Host= 210.227.71.51)(Port= 1521))
???(CONNECT_DATA = (SID = ORCL))

? )

where 210.227.71.51 is Kaiji1 database

svrmgrl >connect system/manager (connected to source database (ORA8) )

sql > create public database link orcl.kaiji1 connect to scott identified by tiger using 'kaiji1';

Itz created with out any error ....

Please ..any one suggest me ..what was the error???

Thanx In Advance

Bye
Magesh

---

This message is confidential and may also be privileged. If you are not the intended recipient, please notify me by return e-mail and delete this message Received on Mon Oct 09 2000 - 04:46:17 CDT