Home » RDBMS Server » Backup & Recovery » how to migrate data from database A to database B
how to migrate data from database A to database B [message #633654] Tue, 24 February 2015 00:55 Go to next message
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 messageGo to next message
Littlefoot
Messages: 21365
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 #633657 is a reply to message #633654] Tue, 24 February 2015 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The question is: what do you want to do with the current tables and data in database B? Keep or leave?

[Updated on: Tue, 24 February 2015 01:29]

Report message to a moderator

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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 65388
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 messageGo to next message
Roachcoach
Messages: 1513
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.
Re: how to migrate data from database A to database B [message #633764 is a reply to message #633661] Tue, 24 February 2015 08:55 Go to previous message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
>i think you are right and this is the only way

@OP, It is still not clear whether you want to migrate the data to a different schema or database.

As Michel said, export/import is a good option. But, if you are dealing with schemas, you don't have to make things so complex.

[Updated on: Tue, 24 February 2015 09:00]

Report message to a moderator

Previous Topic: DBMS SCHEDULER
Next Topic: RMAN-03009,ORA-19502
Goto Forum:
  


Current Time: Fri Feb 23 04:57:50 CST 2018

Total time taken to generate the page: 0.05477 seconds