Summary of methods for Oracle database migrating to MySQL

Time:2020-3-14

Preface

Before that, we built a reporting system framework of extjs + Spring + Oracle. Because of the requirements of other departments, this framework also needs to be customized.

But there is a problem that Oracle’s database needs to be charged. There is no problem with personal use. If the company uses it, there will be infringement.
MySQL is completely free.

So there should be no problem using the combination of extjs + Spring + mysql.

In theory, MySQL has been acquired by Oracle. The migration between the two should be easier, but there are still some problems in the actual migration. Let’s talk about some implementation methods and problems.

Method 1: import and export manually

Manual import means that the operation steps will be more complicated.

For table structure and data:

1. Use SQL developer to export Oracle’s table schema and data (. SQL and. XLS)

2. Use the workbench of Mysql to create tables and import data.

There will be some grammatical differences here, so you need to make some adjustments.

For view, especially for the complex Oracle view with subqueries, it seems not so easy to import into mysql.

Mode 2: use Navicat to import

http://www.navicat.com.cn

Navicat, this is the tool recommended by someone on the official MySQL website. This is a paid software. The current charge is 1000 to 1600 RMB. But you can try it for free for a month.

After downloading and installing, the starting page is as follows:

The migration process is as follows:

1. New database connection

Establish database connection between Oracle and MYSQL to be migrated.

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

Download address: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

After downloading, extract to a directory:

Make the following settings under the tools — > option of Navicat:

For details, please refer to: http://wiki.navicat.com/wiki/index.php/instant’client’required

After setting up, restart Navicat

2. After the connection is set, the table and data can be migrated

Click: tools — “data transmission

Set up connections that require migration in the General tab

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

When the configuration is complete, click start.

Basically, there is no problem with the structure of the table and the migration of data.

But for view import, because MySQL’s view syntax cannot have subquery statements.

On Navite, you can see that when importing from Oracle to MySQL, view’s checkbox cannot be selected.

Mode 3: use the oracle to MySQL of dbmover to import

Dbmover also provides tools for Oracle to MySQL migration.

The download address is: http://dbmover.com/download/oracletomysql.cn.zip

This is also a paid software. The limit of the trial version is that the total number of records allowed to be migrated is 100000.

Download and install. After startup, you will be required to enter the database connection information:

After the configuration is completed, the page is:

You can only see table here.

Compared with Navicat, it seems simple. It can only be migrated to a table, and it is not very convenient to use.

The next time migrate has to input again from the beginning to the end, there is no way to remember the connection configured before.

Mode 4: use Oracle to MySQL of intelligent converters to import

It’s also a charging tool:

Download address: http://www.intelligent-converters.com/oracle-to-mysql.htm

The limitation of using version is that each table can only import 5 data.

The operation mode is similar to that provided by dbremover. The advantage is to remember some of the last connection information.

Similarly, only tables can be imported.

Imported pages:

summary

The above is all about database migration from Oracle to MySQL. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message for communication.

Recommended Today

JVM + GC parsing (premise knowledge concatenation)

Premise preparation JVM GC garbage collection JVM virtual machine monitoring, tuning and troubleshooting Tomcat and microservice optimization 1. Premise review 1.1. JVM memory structure 1.1.1、 JVM Architecture Overview The gray part in the figure isThread private, there is almost no garbage collectionOrange partThread sharing, the main place where garbage recycling occurs What is the class […]