1

I have two databases that have tables with identical schema. I want to compare the two tables. I learned that cross DB queries need a Database Link.

I use SQL developer and here are the properties of the connection that works

Connection Name: MyConn
UserName:SomeUser
password:SomePassword
Connection Type: Basic
Role: default
Host Name: 12.12.12.12
port:2521
SID: xe

I tired this command to create Database link

CREATE DATABASE LINK MyDBLink
  CONNECT TO SomeUser
  IDENTIFIED BY "SomePassword" 
  USING '(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=12.12.12.12)(PORT=2521))
  (CONNECT_DATA=(SID=xe)))';

The command creates a link but when I try to test the link, the connection does not work. The connection times out in 60s.

Am I missing something ?

Silver.Rainbow
  • 425
  • 4
  • 14
  • You probably have a firewall in the way, or possibly have NAT/PAT confusing things. But your comment about MySQL made me wonder - do you actually mean different *databases* in the Oracle sense, or just different schemas (users) in the same XE database? – Alex Poole Jun 17 '19 at 23:20
  • Thanks @AlexPoole. These were different DBs. One had the master data and there was a separate database for each client. I had to frequently query the client DB which involved referencing data in the master database. I'd have to login to the master and the client using separate IDs and I used the [dbname].tablename syntax in the query. – Silver.Rainbow Jun 18 '19 at 13:49
  • Right, but that still sounds more like different schemas within the same database, at least in Oracle terms. [Slightly related](https://stackoverflow.com/a/11618350/266304). And you would still be able to query schema.tablename in Oracle, as long as you'd been granted privileges on the other schema's tables. Let me ask another way; do your SQL Developer connections all use the same hostname, port and SID? – Alex Poole Jun 18 '19 at 13:54
  • No these are separate connections to distinct servers (with different host names) The same was the case with mysql btw. youa re probably right about TCP being blocked by the firewall. – Silver.Rainbow Jun 18 '19 at 13:59
  • OK, well that's ruled out possible confusion of terms and a simple workaround *8-) – Alex Poole Jun 18 '19 at 14:02
  • Thanks you @AlexPoole :) – Silver.Rainbow Jun 18 '19 at 14:03

2 Answers2

0

The Database-Link is between the two databases and not "outgoing" from your local sqldeveloper/client.

So even if you can reach the remote database from your local client, the remote database-server where you created the link might (and in this case indeed does) not reach the target of the database link.

The database link itself is created no matter if the remote database is reachable, or the credentials are incorrect, etc.

If you have the possibility you should directly log on the database server of the database where you created the link and check the network connection to the database server you want to reach from there. Using telnet might help you.

evilive
  • 916
  • 1
  • 7
  • 24
  • Thanks. Unfortunately I can't login to the DB server. I an connect to multiple servers in Sql developers. Is there any other way to query multiple DBs? I could do it in mysql using heidi client by qualifying the table with DB Name. I was hoping for something like that. – Silver.Rainbow Jun 17 '19 at 22:00
  • Well if you can't get a clear network route, you could go for export/import and compare within the same database....which of course strongly depends on the size of the tables and your other requirements – evilive Jun 18 '19 at 22:14
  • Thanks @evilive. Export/import seems to be the only viable option. – Silver.Rainbow Jun 20 '19 at 18:40
0

The best solution is to look at the network or operating system and open a path between the two servers, and then the database link should work. If that's not possible, you can use your desktop PC as a proxy.

The official way to route around network issues is to use Oracle Connection Manager. But in my experience that program is a bit hard to configure.

Another option is to create a database on your desktop, and create two database links to each server. If this desktop database is only used for linking to others, then the free Express Edition should be good enough. If you go down this route, be careful of performance problems when involving 3 databases. You'll probably want to compare hashes instead of actual data, to avoid network problems.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132