Copy data from tables present in diff. schema

[PGoRule]

Hi,

There are two different Oracle servers on different systems with different schema's. I want to copy data(only) from ORA1-Schema1-table1 to ORA2-Schema2-table2. The table structure is same.
Do I need to follow IMP-EXP or is there any other way like insert query in above situation.


Sharing the best from my side...

--Prashant--

 

[slaing]

Hi,

Using a Database Link may be the way to do this.

The first part is creating a database link and getting it to work

Step1 : make sure that server2 has a tnsnames entry pointing at server1 called (eg) server1tns

Step2 : Log on to ORA2 as schema2 and create the link by issuing :
CREATE DATABASE LINK my_link CONNECT TO schema1 IDENTIFIED BY password1 USING 'server1tns';

Step3 : Test it by doing
SELECT COUNT(*) FROM table1@my_link;


To copy the data you would then log on to ORA2 as schema2 and issue a statement like

INSERT INTO table2 SELECT * FROM table1@my_link;



A few other thoughts on database links :

Oracle doesn't test the link at the CREATE DATABASE LINK stage - the link will be created as long as the syntax is correct.
Any or all of the lowercase parameters above can be complete nonsense and Oracle will still create the link.

If you have global_names set to true then there are rules about the naming of the link and the tns entry and the instance.
Hopefully you won't have global_names set to true.

Links between Oracle 9 and Oracle 7 or earlier will NOT work, and imp/exp is your best bet.


Steve

 

[Turkbear]

Hi,
The other common 'gotcha' for database links is forgetting that it is the tnsnames.ora on the database server that must have the entry for the linked instance.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[PGoRule]

Hi,

Thanx slaing...for this wonderful approach.
Thanx Turkbear...



Sharing the best from my side...

--Prashant--