Copying data from one schema to another

[drkestrel]

I have got two db schemas with the same structure:
db1@machine1
db2@machine2

I want to copy certain records of a given criteria (e.g.

 WHERE user_id=drkestrel and where record_id>99

) of one table from db1@machine1 to db2@machine2.

How could I do that? I know you could use

connect

to connect to another schema in SQL Plus, but not sure how that could be used to copy data from one schema to another. Do I need table aliases or something??

 

[sem]

You may create database link between those databases. Then you may transparently select data from both databases.

Regards, Dima

 

[cliousa]

You can do it this way:

1. Create dblink1 on db2@machine2:

CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'dblink1';

(dblink1 is the TNS alias in the db2@machine2 tnsnames.ora file)

2. On db2@machine2 run this DML:

SQL> INSERT INTO db2_user.table SELECT * FROM db1_user.table@dblink1 WHERE user_id=drkestrel and where record_id>99;


That's all. Hope that helps,

clio_usa - OCP DBA
------------------

http://www.dbaclick.com