Immigrate Data

[ligang]

Hi , Friends ,
I'm now thinking a solution to immigrate the data .

The situation is :
Server A : NT , Oracle7.3 . User1 has table T1,T2,T3
Server B : NT , Oracle7.3 . User1 has table T1,T2,T3
(the table name is same and the table structure is also
simular , but in ServerB , the table has some additional columns ) .

Now we want to clone all the data from ServerA , User1 to
ServerB User1 . The data valumn is very big (1million records
in some table and around 1 GB data in User1 ) . Because of the table struture is different , So import and export seems not working.

Please advise .

thanks

ligang

[reydp]

Hi,
One option that you can do is to create a db link for both servers.
Once db link is establish, you can now select certain fields from one db server to another and eventually use this to insert to your target db.
Like:
insert into owner1stdb.table(col1,col2,col3)
as select col1, col2, col3 from owner2nddb.table@dblinkname.

[reydp]

Another thing to add:
You must create a huge rollback segment to be used for this INSERT statement since you have a large number of data.
Example:
SET TRANSACTION USE ROLLBACK SEGMENT HUGERBS;
INSERT INTO OWNER.TAB(COL1,COL2,COL3) SELECT COL1, COL2,.......... FROM OWNER2.TAB@DBLINKNAME;

[ratcheer]

Another way to accomplish this would be to use SQL*Plus on servera to spool out all the data to text files, then use sqlldr on serverb to load them.

[ligang]

Thanks for all of your reply , My senior told me that
it's fast and save if we do like :
1. Create User2 in Server2 , and export from server1 User1
and import to User2 .
2. use insert into user1.t1 as select * from user2.t1
because no dblink required , the speed should be fast .

How do you think the above idea .

[julian]

Originally posted by ligang
Thanks for all of your reply , My senior told me that
it's fast and save if we do like :
1. Create User2 in Server2 , and export from server1 User1
and import to User2 .
2. use insert into user1.t1 as select * from user2.t1
because no dblink required , the speed should be fast .

How do you think the above idea .

I disagree a bit. He says that exp/imp + insert into as select is quicker than just insert into as select via DB link?

I wouldn't bother even testing that. I would do it via the link. But that'e me :-)