1

I have access to one database server through a vpn connection and another through lan connection.

from my machine I would like to copy some entries from one table into the other, like:

insert into destinationtable select * from sourcetable@databaselink where id in ('id1','id2','id3')

Establishing a database link does not work because the servers cannot see each other. Is there any other way to do it?

NotGaeL
  • 8,344
  • 5
  • 40
  • 70
  • 1
    Other than file export/import, I would think not. – OldProgrammer Jul 06 '15 at 13:54
  • so the old export/import it is :-( Bummer – NotGaeL Jul 06 '15 at 14:08
  • 1
    What about SQL\*Plus' `copy` command? (although deprecated it should be fine for a one-time thing) –  Jul 06 '15 at 14:22
  • Using "copy" is an excellent suggestion. I'd also suggest considering a Java/C# program that runs on your machine and connects to both databases, allowing you to shuffle data between the two. – TenG Jul 06 '15 at 18:53
  • Can you set up an SSH tunnel to one of the DB servers and its listener, and create a DB link over that? Of course, doing so would potentially breach the VPN and might not endear you to your security people... Export/import is perhaps safer (though still assumes you're allowed to copy the data in the first place). – Alex Poole Jul 06 '15 at 22:39
  • No, I don't have any access to the database servers other that oracle sql connection. – NotGaeL Jul 07 '15 at 05:13

1 Answers1

1

Install a database on your desktop, create links to both databases, and change the statement to something like this:

insert into destinationtable@databaselink_destination
select * from sourcetable@databaselink_source where id in ('id1','id2','id3')

You may need a virtual machine to bridge the networks. Set the host to one network and set the guest to another, and configure the VM to use both connections.

Depending on your exact configuration you may have additional issues, like licensing, OS privileges, etc. But I've found there is almost always a way to workaround those limitations.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • That is clever! I don't know if I can install a server on my machine though. Licensing and all that... But the simplest alternative so far. – NotGaeL Jul 06 '15 at 19:44
  • Licensing probably isn't an issue. The standard [developer license](http://www.oracle.com/technetwork/licenses/standard-license-152015.html) makes Oracle free for a lot of development uses. Or you can buy [Oracle Personal Edition](https://shop.oracle.com/pls/ostore/product?p1=database&p2=oracledatabase&p3=oracledatabasepersonaledition&p4=&p5=) for about $460. Or, if this is just a one-use system, you could even use Express Edition for free. One nice thing about Oracle is they don't nickel and dime developers ... they $5K and $10K your company for production. – Jon Heller Jul 07 '15 at 04:19