| Database Link Issue [message #63978] |
Wed, 01 December 2004 15:51  |
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 #63981 is a reply to message #63978] |
Wed, 01 December 2004 20:30  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Did you login as system at salesdb and query sales ?
Maybe SYSTEM is looking at a different SALES table then you think!
btw, don't use the user SYSTEM for these things.
hth
|
|
|
|
| Re: Database Link Issue [message #63984 is a reply to message #63978] |
Wed, 01 December 2004 22:58 |
easyweb
Messages: 3
Registered: October 2004
|
Junior Member |
|
|
1.Make sure that inserted data are 'commit' ed in MANDB.
2.Run the same SQL in MANDB without DB link and check.
3.Use schema_name.table_name in FROM part.
webmaster.
www.easywebtech.com
|
|
|
|
| Re: Database Link Issue [message #64007 is a reply to message #63984] |
Sun, 05 December 2004 17:19 |
Gulab Basha
Messages: 7
Registered: June 2003
|
Junior Member |
|
|
Hi,
Thanks for your reply.
I tried using only Schema_name.table_name and I am not successful.
Is there any setting I am missing out.
Please advice.
Thanks,
Gulab Basha.
|
|
|
|
| Re: Database Link Issue [message #64008 is a reply to message #63981] |
Sun, 05 December 2004 17:20 |
Gulab Basha
Messages: 7
Registered: June 2003
|
Junior Member |
|
|
Hi,
Thanks for your reply.
I have tried accessing using System and also general user. But it always return 0.
Is there any setting I am missing out.
Please advice.
Thanks,
Gulab Basha.
|
|
|
|
| Re: Database Link Issue [message #64032 is a reply to message #64008] |
Tue, 07 December 2004 21:06 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I think there are simply no records in the tables !
Connect as system to your remote database and copy and paste the results of the select count(*) there.
hth
|
|
|
|
| Re: Database Link Issue [message #64041 is a reply to message #64032] |
Wed, 08 December 2004 15:52 |
Gulab Basha
Messages: 7
Registered: June 2003
|
Junior Member |
|
|
Hey Frank,
Thanks for your time. I solved the problem by selecting the option suggested by Metalink. I am able to see the records now. The command that I used to create database link is:
CREATE PUBLIC DATABASE LINK "SALESDBLINK" CONNECT TO "SALES_OWNER" IDENTIFIED BY "SALES_OWNER" USING 'TNS ENTRY';
It works for me.
Many Thanks for your time.
Best Regards,
Gulab Basha.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
