how to migrate data from database A to database B [message #633654] |
Tue, 24 February 2015 00:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fa919ca3800934a4df490d762004cc75?s=64&d=mm&r=g) |
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Hi every one
i have database A that working 1 year , now i want transfer data of database A to database B (database B is including structure only) but database A and B have some differences between tables.
some tables have less column and some have more.second issue is in database B the structure maybe normalized (resulting to have more tables) whereas in Database A all fields are stored in the same table.
how can i migrate data from database A to B ?
Thanks
|
|
|
Re: how to migrate data from database A to database B [message #633656 is a reply to message #633654] |
Tue, 24 February 2015 01:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21813 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Regarding differences you mentioned, it looks like a manual job, i.e.
insert into some_table@database_B (col1, col2, col3, ...)
select col1, col34, col2, ...
from some_table@database_A
What do you call a "database"? Is it, by any chance, a "schema"? That would make things simpler and faster (you wouldn't need a database link).
|
|
|
|
Re: how to migrate data from database A to database B [message #633659 is a reply to message #633657] |
Tue, 24 February 2015 02:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fa919ca3800934a4df490d762004cc75?s=64&d=mm&r=g) |
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Quote:The question is: what do you want to do with the current tables and data in database B? Keep or leave?
i understand your question but i dont understand the reason that u ask such a question.however i want terminate the old database (database A) and use the new database (database B) this because of upgrade and deploying software that working with new structure of database .
[Updated on: Tue, 24 February 2015 02:02] Report message to a moderator
|
|
|
Re: how to migrate data from database A to database B [message #633660 is a reply to message #633656] |
Tue, 24 February 2015 02:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fa919ca3800934a4df490d762004cc75?s=64&d=mm&r=g) |
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Quote:Regarding differences you mentioned, it looks like a manual job, i.e.
insert into some_table@database_B (col1, col2, col3, ...)
select col1, col34, col2, ...
from some_table@database_A
What do you call a "database"? Is it, by any chance, a "schema"? That would make things simpler and faster (you wouldn't need a database link).
thanks i think you are right and this is the only way
|
|
|
Re: how to migrate data from database A to database B [message #633661 is a reply to message #633659] |
Tue, 24 February 2015 02:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
arsia wrote on Tue, 24 February 2015 09:01...
i understand your question but i dont understand the reason that u ask such a question...
The purpose is that if you don't want to keep the tables and data in database B then you can just export/import those from database A.
This is faster than many INSERT SELECT across a db link and keep the source constraints, indexes and so on.
[Updated on: Tue, 24 February 2015 02:09] Report message to a moderator
|
|
|
Re: how to migrate data from database A to database B [message #633762 is a reply to message #633661] |
Tue, 24 February 2015 08:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
You might find better results exporting DB A and importing to DB B remapping schemas just to get the data in, depending on how complex the remappings are. The optimizer has a bad habit of making a mess of queries over DB links. If you stuff it all in the same database temporarily, you might get better net performance. You'll have to test.
|
|
|
|