Re: How to copy data from one database to another

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Tue, 2 Mar 1999 21:05:27 +0100
Message-ID: <36dc448d$0$24367@newton>

Nicolas Bronke wrote
>connect to DB1 and connect to DB2
>update db1.Table set field = (select field from db2.table where ...)

You can use a database link. For some dark reason, this link should have the same name as your database, which is oracle by default. Make sure that db2 is known in tnsnames.ora and note the quotes in " using 'db2' ":

create database link oracle

        connect to scott
        identified by tiger
        using 'db2';

connect scott/tiger_at_db1

update table1 a
set a.field =

        ( select b.field
          from table2_at_oracle
          where b.id = a.id
        );

drop database link oracle;

By the way, note that executing

select sysdate_at_oracle
from dual;

yields the system time of db1, not db2 (the package STANDARD of db1 is used). Should you ever need to now to remote time, then you need to define a stored function in db2 that simply returns sysdate...

Arjan. Received on Tue Mar 02 1999 - 14:05:27 CST