Re: How to copy data from one database to another

Nicolas Bronke wrote
>The dblink working fine (but only one! if I assign another
>to another database, all links refer to only one).

That is the exact reason why one should not use the default database name when creating a new database, to avoid that only the database link name oracle can be used. Still, you can set some Oracle option, allowing you to give a database link any name you like, but Oracle states that it is only for backwards compatibility...

If databases have different names, then it is no problem to define and use more than one database link:

    create database link db2 connect ... using 'db2';     create database link db3 ... using 'db3';

    connect scott_at_db1

    create table my_table as

        select a.col1, b.col2, c.col3, d.col4
        from tableA a -- local db1 table
        , tableB_at_db2 b
        , tableC_at_db2 c
        , tableD_at_db3 d
        where a.id = b.id
        and a.id = c.id
        and c.id2 = d.id;

Of course, there is no need at all to drop a database link if you plan to use it more than once.

Arjan. Received on Tue Mar 02 1999 - 15:09:56 CST