Home » Server Options » Replication » Database Link Issue
Database Link Issue [message #75583] Wed, 01 December 2004 15:48 Go to next message
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 #75584 is a reply to message #75583] Wed, 08 December 2004 10:54 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Here are a couple of things to check:

1. Do you have data in this table? Check on the source system.

2. Does your DB link point to the right database - do a "select * from global_name;" to check.

3. Is the DB link connecting to the right user - do a "select username from dual@DBLINK;" to check.

Best regards.

Frank
Re: Database Link Issue [message #75591 is a reply to message #75583] Tue, 21 December 2004 00:14 Go to previous messageGo to next message
Nvgpal
Messages: 3
Registered: December 2004
Junior Member
Try this way append the user name to that table

SELECT COUNT(1) FROM SALES_OWNER.SALES@SALESDBLINK;
Re: Database Link Issue [message #112845 is a reply to message #75591] Tue, 29 March 2005 11:41 Go to previous message
tarun123v
Messages: 9
Registered: March 2005
Location: Hyderabad(India)
Junior Member

Did u use Materialized View if u have used Materialized View
this line should work
select * from sales sales@linkname;
you need not give the owner this will be taken care by Materialized View
if you have not used this view then you need to give the owner name
Previous Topic: Replication -Primary key
Next Topic: Simple Replication Help Needed
Goto Forum:
  


Current Time: Thu Mar 28 20:03:13 CDT 2024