Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL scripts for data migration

 
Raj Kumar Bindal
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have to migrate data from one database to another database using sql scripts. So, i need to write sql scripts for the migration. I have table names/depencies available with me for the data that needs to be migrated. Is there anything else that i need to take care while migrating the data? As of now i know there are two ways of writing sql scripts for this:

1. Using database links
2. insert into db2.tablename select * from db1.tablename //not sure if this will work fine.

Please tell me the best way for writing the scripts.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Raj Kumar Bindal wrote:Hi,

I have to migrate data from one database to another database using sql scripts. So, i need to write sql scripts for the migration. I have table names/depencies available with me for the data that needs to be migrated. Is there anything else that i need to take care while migrating the data? As of now i know there are two ways of writing sql scripts for this:

1. Using database links
2. insert into db2.tablename select * from db1.tablename //not sure if this will work fine.

Please tell me the best way for writing the scripts.

What have you described is actually one way. To access another database directly, you need the SQL link, then you could run scripts that would do it. However, the correct syntax to access remote table is table@remote_database.

Anyway, using database links is not trivial. I'd suggest using the data pump export and import tools (expdp/impdp) to copy the data instead, it takes case of grants, constraints and indexes. These tools are not trivial either, but are well described in Oracle's documentation and less things can go wrong with them.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic