Four ways to import Oracle data into MySQL

I. Data migration tool in Navicat Premium

To release some of the resources for the production library, you need to migrate the API modules to MySQL and need to guide the data.

After trying the Oracle to MySQL tool, the migration times did not say that such a large amount of data was unreliable with this crude tool.

Accidental discovery of the usual database view tool Navicat Premium has a data migration tool that is surprisingly useful. This tool natively supports the MYSQL,ORACLE,SQLLITE,POSTGRESQL database, and therefore provides the ability to migrate data between different databases.

Before migrating, make sure you set up the connection for these two databases. Select Tools/datatransfer.

Select the source database, select the table you want to move, and the target database.
< yo? " http://www.2cto.com/kf/ware/vc/"target=" _blank "class=" Keylink ">vcd4kpha+pgltzybzcmm9" http://www.2cto.com/ Uploadfile/collfiles/20141101/2014110109051329.png "alt=" \ ">

Select the perimeter.

The migration process, the upper-left corner is progress.

----------------------------------------------------------------------------------------------------------- ------------ two. Use MySQL migration Toolkit to quickly import Oracle data into MySQL
quickly import Oracle data to MySQL using MySQL migration toolkit
come on, start talking nonsense.
I recently learned some knowledge of the database, prior to contact with Oracle and MySQL, and recently very popular MongoDB non-relational database, so simply study together, compare learning to find different, first of all, I use the database version and visualization tools
Oracle10g-pl/sql Developer9
Mysql5.5.29-mysql Workbench6.0
MongoDB2.4.9 (32bit max 2G)-robomongo0.8.4
In order to maintain the consistency of the data, the existing Oracle part of the data to be ported to MySQL, after Baidu found that MySQL migration toolkit is good, will now write the steps to share with you
First, install MySQL migration Toolkit
Go to http://dev.mysql.com/downloads/gui-tools/5.0.html to download MySQL GUI tools (which is actually a MySQL management tool), including MySQL migration Toolkit tool, next installation complete
Second, the first operation needs to load the Ojdbc14.jar package

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, then restart the MySQL migration Toolkit.

Third, after the driver is loaded, come to the source database interface will become the form below, in which to fill in the Oracle database connection information, press "Next" to continue.

Iv. 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.

After the connecting to server test pass, press "next", to Source schemata Selection, click "Next" to continue after selecting the database for data migration.

After reverse engineering test passed, press "Next", in Object Type Selection, point detailed Selection button, in the bottom left list select the table not to be migrated, put it into the right list, That is, the remaining tables on the left-hand list are migrated for data. Select OK and press "Next" to continue.

migration of type Oracle Schema in object mapping, 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.

Eight, after the migration test passed, and then to Manual Editing, where you can repair the conversion 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.

Nine, in the object Creation options, select the Local Disk storage data table structure, press "Next" to continue.

Ten, after creating objects to create all the table structure is complete, the table does not have data, press "Next" to continue.

Xi. "Next" to the data Mapping options, select Local disk to store data in the datasheet and press "next" to continue.

12. After bulk data transfer to create all the tables, press "Next" to continue.

13, came to summary display this data conversion information, can be saved as a file, press "Finish" to complete.

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 % '; Modify this parameter by locating the My.ini file in the MySQL folder and adding 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.
14. Import data using MySQL Workbench
Click Data import/restore to import the table structure first, then import the table data once, complete the database migration

----------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- three. first the data in the Oracle table is in Excel table, then import the data in Excel table into MySQL

This is to import data from the table Eventlogs in Oracle into MySQL. The steps are as follows:

1. Use select * in PL/SQL to search all data in the Eventlogs table, then right-click on "Copy to Excel" as shown in:

2. Save the data as an Excel table and rename it as follows:

3, open the MySQL visualizer, I here is navicat, select the table, click the Import Wizard, as shown in:

4. Select "Execel file" in "Import Type" and click "Next", as shown in:

5, Next will let you select the file, select the Excel file you have saved, and select "SQL Result" as shown in:

6, then always click Next until Step 6, fill the Target field. The first field is usually empty and is filled in according to your needs. I am here to fill in the "_id" and set the primary key. As shown in the following:

7, then always click Next, the last step click Start. A table of "SQL Result" appears, renamed to the table you want. ：

The data has been imported. If the data type in the table is not what you want, you can design the table again.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -- Four, manually import Navicat
This may be the stupidest and least effective way, but it is also the most reliable way. On the premise that none of the above 3 methods are applicable, a single sheet is built in Navicat for MySQL. Alternatively, the. sql file exported from Oracle cannot be imported by running the SQL file in Navicat because some of the field properties do not apply to mysql,oracle and the MySQL syntax and field properties are different, so create the table SQL statement one by one is modified and then run in Navicat to create the table.

Four ways to import Oracle data into MySQL