| Database Link Issue [message #74380] |
Wed, 01 December 2004 15:49  |
Gulab Basha
Messages: 7
Registered: June 2003
|
Junior Member |
|
|
Hi there,
I am facing strange issue with Database Link. The issue is Database Link is successfully created but when I select the data from the tables it returns no rows.
To give you complete details. I have two databases in different servers.
First Database SALESDB in Server1 and MANDB in Server2. I have created tns entry in Server2 pointing to SALESDB that is in Server1.
I have created database Link by connecting SYSTEM/MANAGER@MANDB in Server2. The command was:
CREATE DATABASE LINK SALESDBLINK CONNECT TO SALES_OWNER IDENTIFIED BY SALES_OWNER USING 'SALESDB';
I tried to Select records in several ways:
1. SELECT COUNT(1) FROM SALES@SALESDBLINK;
It returns the Zero where as the records are present in the actual table.
2. CREATE SYNONYM SALESSYN FOR SALES@SALESDBLINK;
SELECT COUNT(1) FROM SALESSYN;
It returns the Zero where as the records are present in the actual table.
3. CREATE OR REPLACE VIEW SALESVIEW AS SELECT COUNT(1) COUNT_SALES FROM SALES@SALESDBLINK;
SELECT * FROM SALESVIEW;
It returns the Zero where as the records are present in the actual table.
I am wondering why the records are not retrieved by this DBLINK. Is there anything I am missing out in setup.
For your information I have also tried same steps with PUBLIC DATABASE LINK too.
Could you please give me suggestions or clues so that I can overcome this issue.
Many Thanks,
Gulab.
|
|
|
|
| Re: Database Link Issue [message #74409 is a reply to message #74380] |
Mon, 06 December 2004 09:33  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Make 100% sure you are pointing to the right instances. There is a nasty old bug related to old listeners not understanding the "SERVICE_NAME=" syntax in tnsnames.ora files. It caused the connection to be made to the first instances in the remote listener rather tjat the instance you specified. Having different passwords for the same accounts on different servers would prevent that. Using "global_names=true" would probably also prevent that.
Select instance_name from v$instance@SALESDB;
select global_name from global_name@SALESDB;
Select instance_name from v$instance@MANDB;
select global_name from global_name@MANDB;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
