Quickly import Oracle data using MySQL Migration toolkit

Recently I have a project to import the original Oracle database data into MySQL, after trial found MySQL GUI tools MySQL Migration Toolkit can be very convenient to the Oracle data into MySQL, Especially for CLOB, blob type of data also have very good support. The following author describes the use of MySQL migration Toolkit, the software can be downloaded to the http://dev.mysql.com/downloads/gui-tools/5.0.html.

1. Run MySQL migration Toolkit, "Next" to "Source database", select Oracle database Server in database system, If the first use will tell you to load the driver Ojdbc14.jar.

　　

After the driver is loaded, the interface becomes the following form, in which you fill in the connection information for the Oracle database and press "Next" to continue.

　　

2. In target database, the default database system is MySQL Server, in connection parameters, fill in the corresponding MySQL database connection information, press "Next" to continue.

　　

3, the database connection test, the test passed after the "next" to the source schemata Selection, click to prepare for the data migration schemata after Press "next" to continue.

4. In Object Type Selection, click Detailed Selection button, select the table that is not migrated in the list on the left, and put it in the list on the right, that is, the remaining tables on the left list will be migrated. Select OK and press "Next" to continue.

5. In object mapping of the migration of type Oracle Schema, if you want to set parameters, click the Set Parameter button. If the default database table is UTF8, select MultiLanguage, and if the default database table is GBK, you will need to select User defined and fill in CHARSET=GBK, collation=gbk_general_ci below. Migration of type Oracle table to set the parameter set parameter button. If the default database table is UTF8, select data Consistency/multilanguage, and if the default database table is GBK, you need to select User defined and fill in addautoincrement=yes below. CHARSET=GBK, Collation=gbk_general_ci, Engine=innodb. Select OK and press "Next" to continue.

6, and then to Manual Editing, here can repair alterations table script. Due to the differences in syntax rules between Oracle and MySQL, it is often necessary to adjust the data type of the script and the default values, such as the default value sysdate is usually set for timestamp type data in Oracle, but it is not recognized in MySQL. Select Show All Objects in the filter and select the table in the migrated Objects to modify the script, then click Advanced in the lower left to edit the script. Click on the right side of the Apply Changes button to save and press "Next" to continue.

7, then all the way "Next", if there is no prompt error, you can import data from the specified table into MySQL. If you are prompted with an error message, you need to return to manual editing to re-modify the script.

It should be added that in the case of large capacity data, especially CLOB data, an exception may occur: "Packets larger than Max_allowed_packet is not allowed". This is because the MySQL database has a system parameter Max_allowed_packet, the default value is 1048576 (1M), you can query its value in the database by the following statement:

Show VARIABLES like '%max_allowed_packet% ';

To modify this parameter, locate the My.ini file in the MySQL folder and add a line in the My.ini file [mysqld]:

max_allowed_packet=16777216

Restart MySQL so you can import less than 16M of data, of course, this value can be adjusted as needed.

Original source: http://www.cnblogs.com/dev2dev/archive/2010/03/30/1700320.html

Quickly import Oracle data using MySQL Migration toolkit