Retrieving data from another database

[willem05]

Hi all,

I am dealing with an interesting problem. We have an Oracle 817 database in the UK and an Oracle 817 database in Holland. These two databases are completely independant from eachother.

How do I retrieve data from the db in the UK and insert it into our (Oracle) database without setting up a dblink (kinky thing to my opinion) and without a sqlplus-copy (takes too long). At this moment I am retrieving the data into ms-SQL2000 without any problems and it is fast. To my opinion, Oracle817 should be much faster .

Please help...

Kind regards,
Willem

 

[sem]

Are you sure that Oracle and not the network is a bottleneck? Do you exchange data between MS-SQLs in Holland and UK? And why do you reject dblink? Our servers are in single country, but some distances are >500Km and we do use dblinks for quite large queries. Of course for "more than quite large" amounts we use export/compress/import or query/compress/loader

Regards, Dima

 

[Turkbear]

Hi, and exactly how do you think ms-SQL2000 is doing it?

AFAIK,
The only way to get something from one place and put it somewhere else is either to be connected to both places at once ( db link or Sql Copy) or connect to one place (uk for instance), get the data, spool it out to some SqlLoader compatible format, disconnect, connect to the local instance ( Holland),load the data!
This is way kinkier than a db link.

After all, db links and the Copy command were designed to do that kind of task.

 

[willem05]

Turkbear and Sem, you might be right that a dblink would be a more prefered solution. The bottleneck btw is the network (I Think). 3million records takes 4 hours.. And that is only 1 table.
Is it simple to setup a dblink without problems ?? In our old situation (Oracle7) it caused several problems when the connection between the two sites went down (WAN). When the line went down, it took the DB also down.

I read some things about setting up an dblinnk. Is that to be done from one side, or should both sides have this setup ??

 

[Turkbear]

Hi, the DB Link ( at least all the ones I have used) is pretty stable ( when the network goes down, the link does not work, but should not take anything down )..

A db link is needed on any location that wants to get or put data from/to some other remote location, but only the link on the site doing the action is in use at any one time.
The only 'gotcha' I have run into is failing to insure that the tnsnames.ora files on the SERVERS involved in the link are correctly configured [ too often, I have forgotten that the client tnsnames.ora file is NOT used to resolve the connection - it is a server - to - server connection]



PS: Given the size of the data, and depending on the scheduling needs, Exp/FTP/Imp to staging_tables/loading_into_target_tables may be a better solution since you could do it in off hours as a cron job ( or something like that)and some triggers.

 

[sem]

I suppose that moving 3 million records through dblink is not very efficient. You may use either of ways suggested above. BTW how do you move them with MS-SQL in shorter terms?

Regards, Dima

 

[willem05]

I use DTS for that. I simply create a DTS and retrieve all the data over an ODBC-connection. This is very simple, but since it is Microsoft's, not very stable. It works... That is all I can say. Retrieving the data at night is crucial (network/performance) so that is why I wanted to have this scheduled by Unix/Oracle. All the datawarehouse-queries, I can do with SQL-server...

Regards,
Willem

 

[sem]

Do you mean that retreiving 3 million records via ODBC is faster than the same done via dblink??? It contradicts completely to my own experience.

Regards, Dima

 

[willem05]

No, retrieving the 3 million records with odbc is faster than using the sqlplus-copy command. With DBlink, as far as I can guess, should be faster.

Regards,
Willem