Database migration from Oracle to MySQL

Preface

A framework for a reporting system such as ExtJS + Spring + Oracle was previously built. Because of the requirements of other departments, the framework is also required to make some special customizations.

But one problem is that Oracle's database needs to be charged, and personal use is no problem, and the company will have a problem with infringement.

and MySQL is completely free.

So using a combination of ExtJS + Spring + MySQL should be fine.

In theory, MySQL has been acquired by Oracle, the migrate between the two should be relatively easy, but the actual migration is still a few problems, the following is a few ways to say how to achieve and problems.

method One: Import Export manually

Manual way to import, that is, the operation of the procedure is more cumbersome.

Structure and data for table:

1. Use SQL Developer to export the schema of the Oracle table and Data (. SQL and. xls)

2. Use MySQL WorkBench to create Table and import data.

There's a slightly different syntax here, so you need to make some tweaks.

For view, especially the complex Oracle View with subqueries, it doesn't look that easy to import into MySQL.

method Two: Import using tool Navicat

http://www.navicat.com.cn

Navicat, this is the tool that someone on the MySQL official website suggested to use. This is a fee-free software. The current fee is 1000 to 1600 RMB. But you can try it for free for one months.

After downloading the installation, start the following page:

The migration process is as follows:

1. Connection to the new database

Establish database connections for Oracle and MySQL that need to be migrated.

In addition, you need to download a oci.dll file when you establish an Oracle connection.

：

Http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

After downloading, unzip to a directory:

In Navicat, under the tools---options, do something like this:

Details can be found in:

Http://wiki.navicat.com/wiki/index.php/Instant_client_required

After Setup is complete, restart Navicat

2. After the connection has been set up, the migrate of the table and the data can then be

Click: Tools--"data transfer

Set up a connection that requires migrate in the "General" tab

In the "Advanced" tab, set what specific content you need to migrate:

After the configuration is complete, click "Start" to do so.

Basically, there is nothing wrong with the structure of the Table and the migration of the data.

However, for view import, the syntax for MySQL view cannot have subquery statements.

On Navite, you can see that when you import from Oracle to MySQL, the checkbox for View cannot be selected.

method Three: Import using tool dbmover Oracletomysql

Dbmover This site also provides tools for Oracle-to-MySQL migration.

Is:

Http://dbmover.com/download/oracletomysql_cn.zip

This is also a charge of the software, the trial version of the limit is: allow the migration of the number of records accumulated 100,000.

Download the installation, you will be asked to enter the database connection information before starting:

The pages that have been configured after the completion are:

You can only see the table here.

Compare with Navicat, feel this appears simple, can only migrate table, and use on also is not very convenient.

The next time migrate again to the end of the input once, there is no way to remember the previous configuration of the connection.

mode four: Import using tool intelligent-converters Oracle-to-mysql

The same is a charge tool:

：

Http://www.intelligent-converters.com/oracle-to-mysql.htm

The limitation of the usage version is that only 5 data can be imported per table.

The operation is similar to that provided by Dbremover. The advantage is that you can remember some of the last connection information.

It is also possible to import only tables.

Imported Pages:

Database migration from Oracle to MySQL