Oracle dblink problem

[BGiulio]

Hello Gang,

I'm facing a strange problem with the use of a dblink, here i have put some details of the issue:

My database: an oracle 8.01
Remote db oracle 9i

I've been granted the access to the remote db with 'remuser'/'rempwd'

1st step: creation of the TNS service name 'RemoteService'to the remote db, I tested it and it works

2step: creation of the dblink in my database:

"create public database link linkname connect to remuser identified by rempwd using 'RemoteService'"

Now, if i connect using Sql+ with 'RemoteService' and remuser/rempwd, I'm able to perform my query

select * from ownername.nametable;


If with Sql logged to my database service I launch the below select:

select * from ownername.nametable@linkname;

the Sql session hangs without returning anything, nor errors neither data.


Do you have any idea of what the problem could be?


Thanks already!

[Brian Crabtree]

When you say that it is hanging, do you let it sit for a while waiting, or do you cancel it?

Can you get the session wait event for the session as well?

You will need to get your current sid (select distinct sid from v$mystat) and then run the select statement, and in another session perform "select * from v$session_wait where sid = ".

Thanks,

Brian

[Robert Howie]

Hi there,

Ever find an answer to this one?
Got the same problem myself. Is it something to do with /etc/hosts not having the remotes servers IP address? Hosts is owned by root so changing it just to try is a hassle.

Thanks,
Bobby

[BGiulio]

Hello,

the problem was in remotenetservicename definition: it was defined into the tnsname.ora of my pc and hence I was able to log in using my oracle client
But when I tried from sqlworksheet of my database, since that name was not into init.ora parameter file, it was not recognized


You should use the following command
"create public database link linkname connect to remuser identified by rempwd using 'RemoteService'"

with an explicit definition for remoteservice instead of using the name alias defined only on your pc

Cheerz!


Giulio