Re: Help: Database Links

From: L120bj <l120bj_at_aol.com>
Date: 1997/03/17
Message-ID: <19970317210201.QAA04005@ladder01.news.aol.com>#1/1

This sounds very similar to a problem we experienced recently. If I remember rightly we had the same symptoms as you, but had discovered something even more curious, in that if you created a synonym using the defined database link
eg create synonym grumpy_tabs for tabs_at_grumpy; then we could execute
select * from grumpy_tabs;
but not
select * from tabs_at_grumpy;

When this happened I thought I was going mad ! We rang ORACLE support, expecting to be told it wasn't possible, but amazingly enough chanced upon somebody who actually knew what the problem was. Apparently the network protocol (in our case TCP/IP) was linked into the ORACLE kernel, which was why the synonym worked, but not into sqlplus, hence the select from tabs_at_grumpy didn't work. Once we relinked the sqlplus executable using the ORACLE makefile everything worked as expected. Hope this helps, I'd be interested to know the outcome.

All the best
Rob

---

---

Subject: Help: Database Links
From: Rob & Wendy Carpenter <chaochou_at_ix.netcom.com> Date: Fri, 14 Mar 1997 19:16:03 -0700
Message-ID: <332A0663.4EB_at_ix.netcom.com>

I have been attempting to get dataabse links to work for some time. I am using v. 7.3.2.2.0 for NT and have two workstations called Grumpy & Computer1. The distributed option is in place [at least as shown by SVRMGR23 and SQL*Plus].

The network between them is using Named Pipes as a protocol and the *.ora files were constructed with Net Manager. The basics are:

		Computer1			Grumpy
		---------------			--------------
Host		Computer1			Grumpy
Global_Name	III.World			Oracle.World
db_name		III				ORACLE
SID		III				ORCL
TNSNAMES	III				Grumpy
Community	NMP.World			NMP.WORLD
Listener	Listener			Listener

Aliases were created and there is an alias in the TNSNAMES file on Computer1 entitled Grumpy. The working of the basic network is checked with the following:

(1) TNSPING -- OK
(2) from SQL*Plus ----> connect uid/pwd_at_grumpy -- OK

    All functionality is fine.
(3) from PowerBuilder with the connection string '@TNS:grumpy'

    which looks to the TNSNAMES file for direction [hence @TNS }     all works fine.

A database link is created:

CREATE PUBLIC DATABASE LINK ___1____
Connect to UID identified by PWD
using '__2___'

with the blanks as Grumpy, GRUMPY.WORLD, ORACLE.WORLD, ORACLE.GRUMPY.WORLD, and virtually every other combination I could think of, created the dataabse link. Query of dba_db_links showed that. But on attempting to execute a query,

	select table_name from tabs_at_grumpy 
	[or whatever the name of the link was per try]

one of two responses is obtained:

	Unable to resolve name
	unable to connect

Per the documentation [Distributed Databases v. 1] ___1___ is the name of the link which must equal the service name [i.e. ___1___ and ___2___ must be equal]. This was tried in conformity with this rule and without conformity with this rule. There appeared to be no difference in the result. To be sure that permissions were not the issue, the same approach was tried with UID = system/PWD = manager system/manager has the create session privilege.

I would appreciate any advice on how to create the link so that it does not fail at query.

Thank you. Received on Mon Mar 17 1997 - 00:00:00 CST