This content has been marked as final. Show 4 replies
SQL Developer has 2 ways to move data.
1) Online Data Move.
This is the way data is moved in the Quick Migrate or if you use Migration>Move Data. It uses the MySQL JDBC driver you specified.
This is really only for small datasets. Its compounded by the MySQL JDBC driver which by default returns all the rows, into memory, when a table is queried.
2) Offline Data Move.
SQL Developer will create for you scripts which utilize MySQL mysdump and Oracles SQL*Loader. These tools are designed to move data around. The scripts dump the data in .dat files and then takes these files and load them into your table. This is the recommended approach for larger data sets. These scripts give you the maximum amount of flexibility as well, allowing you to migrate 1 table at a time if you wish.
Note: For Offline Data Move, you need to perform a standard migration with a Capture / Convert / Generate phase. Dont use the Quick Migrate. As you will want to reference the migration repository to create the scripts.
1. How can I migrate individual table data?
Use the Offline Data Move approach and modify the script to load only the table you want.2. What could be the possible causes of no data migrated on the two tables? One has 176,074 rows, the other 1,792,318 rows.
This is most likely due to an out of memory exception as all rows are returned into memory. We have a bug logged against this and we are working on using more advanced solution to move MySQL data online. But the best approach for large datasets is to use the Offline Data Move approach.3. How can I see a detailed list of errors occured during data migratation?
The Offline Data Move scripts use MySQL mysqldump and Oracles SQL*Loader. These tools give lots of feedback in log files. So if issues occur you should be given all the info you require.
4. Is there any way to speed up the data migration? I postponed the index creation until after the data load.
With large datasets, the Offline Data Move will be much more performant as you are using the "real" database datamove tools .
Heres a short viewlet/video of someone doing a SQL Server Offline Data Move. Its very similar in MySQL.
I hope this helps
Thanks for the reply. I tried the offline approach, but got an error when trying to use mysqldump utility to dump data from a table in Mysql database:
I modified the unload_script.sh from
mysqldump -h <HOST> -u <USERNAME> -p<PASSWORD> -T <DESTINATION_PATH> fields-terminated-by="<EOFD>" lines-terminated-by="<EORD>" <DBNAME> pca.H_TEMPLATE_SPLITS
mysqldump -h speech1.research.att.com -u pcaall -p ***** -T /home/pca fields-terminated-by="," lines-terminated-by=";" pca pca.H_TEMPLATE_SPLITS
then run the script, but got the following message
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
What did I miss here?
$ mysqldump -h localhost -u root -pPASSWORD -T /home/turloch/tmp fields-terminated-by="," lines-terminated-by=";" turloch simpletab
works for me,
1/there is no space after the -p and
2//home/turloch/tmp has to be writable by the mysql daemon process.
3/simpletab has no database prefix
4/This is mysql 5.0
I corrected the command as suggested, and use root to login instead, it worked this time. Thank you so much for the help!