Shulou(Shulou.com)06/01 Report--

I. Overview

Now there is a need to transfer the data from the mysql database to the oracle database. I found several ways on the Internet, and finally decided to use oracel sql developer as a tool to achieve it.

Mysql and oracle belong to two different databases, and there are many differences in specific use, so you can't simply use sql files for export and import. The following examples compare the differences between Mysql and oracle databases

L SQL basic grammatical differences

In the SQL statement of MySQL, the table name and alias of the table must be case-sensitive (you can ignore the case by modifying the mysql configuration file my.cnf and adding lower_case_table_names=1 to keep it consistent with Oracle as much as possible), especially the alias of the table. It is suggested that the differences should be eliminated by unifying the development specifications within the group.

MySQL Insert statement, support ignore syntax to ignore primary key conflict error, such as: insert ignore into table_name...; supports insert into. On duplicate key update... At this point, the table must have a primary key or uniqueness constraint field

Similar to the insert statement, MySQL also supports the replace into statement, which replaces the old record with the new data record if the data already exists in the table.

L build table sentence difference

When creating a table, MySQL does not support create or replace, but you can recreate the table through drop table if exists tbl_name.

In addition, MySQL does not support the tablespace suffix in table-building statements and indexing statements. At this point, you can comment out part of the tablespace in the DDL statement exported by Oracle; at the same time, when importing the MySQL database, execute set @ @ sql_mode='oracle' to keep the syntax consistent with Oracle as much as possible. For specific sql_mode, please refer to the official manual of MySQL and other documents.

L built-in function difference

Refer to https://www.cnblogs.com/HondaHsu/p/3641190.html for more information.

L table primary key value difference

MySQL does not support Sequence and can set a self-increasing primary key in the table. If it is compatible with Oracle, you can consider using function to encapsulate the differences in primary key values.

L stored procedure difference

For stored procedures, Oracle's PLSQL is also quite different from MySQL, for example, MySQL does not support packages, does not support constant definition, does not support dbms_output debugging. The details will be sorted out later.

L View difference

Subqueries are not allowed in MySQL's view from statement and can be solved by creating subqueries as new views. The concepts related to materialized views of Oracle also do not exist in MySQL.

2. Environmental preparation 1. Mysql preparation

Mysql creates test data, builds a library, builds a table, and inserts two pieces of data at random

[root@DB mysqldb] # mysql-uroot-p Enter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 23Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. Mysql > show databases;+-+ | Database | +-+ | information_schema | mysql | | test | +-+ 3rows in set (0.12 sec) mysql > create database test1 Query OK, 1 row affected (0.03 sec) mysql > use test1Database changedmysql > create table tb1 (name varchar (20), age int); Query OK, 0 rows affected (0.06 sec) mysql > insert into tb1 values ("tom", 18); Query OK, 1 row affected (0.00 sec) mysql > insert into tb1 values ("jerry", 20); Query OK, 1 row affected (0.00 sec) mysql > select * from tb1 +-+-+ | name | age | +-+-+ | tom | 18 | | jerry | 20 | +-+-+ 2 rows in set (0.03 sec) mysql > exitBye

2. Oracle preparation work

In Mysql, the relationship between the database and the user is not particularly obvious, as long as we link to the Mysql server, we can see all the databases and tables, while the new user in Oracle does not have any resources and permissions, so we need to assign resource permissions for it, and we can also establish tables under this user, but we can not build a database like Oracle, because for oracle, a user corresponds to a database of Mysql. So to migrate the database, you need to create a user in Oracle with the same name as the corresponding mysql database (the mysql database I want to migrate here is test1, and the oracle user name created is test1).

In this case, pay attention to the user name of the oracle database. Because when sql developer converts mysql to oracle database, it will store mysql data under a user, and this user name must be the same as mysql's database name. If the user already exists in oracle, the data is imported directly under the user; if there is no user, sql developer will directly create such a user and import the data under that user. It should be noted that the default tablespace for oracle database users created by sql developer is user, which may lead to full user tablespaces if you don't pay attention to it! Therefore, in this case, it is best to create an oracle user with the same name as the mysql database to prevent the user tablespace from overflowing and affecting the data import.

-- create tablespaces

CREATE TABLESPACE MY_TEST DATAFILE'/ opt/oracle/oradata/test/MY_TEST.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO

-- create users

CREATE USER test1 IDENTIFIED BY test1 DEFAULT TABLESPACE MY_TEST TEMPORARY TABLESPACE TEMP;GRANT CONNECT TO test1;GRANT RESOURCE TO test1;GRANT DBA TO test1;GRANT UNLIMITED TABLESPACE TO test1

3. Installation and configuration of required tools

Oracel sql developer gets the address:

Http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Mysql-connector-java-5.1.44-bin.jar gets the address

Http://download.softagency.net/mysql/Downloads/Connector-J/

1 is an integrated jdk installer that supports the windows 64-bit operating system without having to install your own jdk environment

2 is an installer that does not include jdk and can support windows 64-bit and windows 32-bit operations

3 you can choose the previous versions.

What I download here is sqldeveloper-4.1.3.20.78-x64.zip

Note:

1 if there is an error during installation, you need to scan the registry with 360 and reinstall some patch packs:

2 the following hints may occur at the end of the migration process: an error occurred during capture and invalid parameters in the call

The migration report is as follows:

I checked the online information here and did not find the specific reason, but changed to a lower version of oracle sql developer to re-transplant the operation on OK. So it's best not to use the latest version of oracle sql developer. I don't know if compatibility is involved.

4. Configure oracle sql developer connection mysql:

Select tools-- > preferences, and select Database-- > third-Party JDBC drivers in the pop-up interface.

Create a connection to oracle

Create a connection to mysql

You can see that two databases have been connected.

Start transplanting 1. Delete the previous migration database

In order to fully complete the migration, I don't know if the database has been transplanted before, so first put the original transplant database

two。 Start the migration operation

Select the mysql database to convert and add it to the list:

Specify conversion rules, you can set the transformation of field properties according to your own situation, or you can add new rules. But generally choose the default one to meet the demand.

If only the table structure does not guide the data, check the truncated data.

You can see that several files have been generated under the output directory. Open the first two to have a look:

IV. Data verification

In Oracle sql developer, under HSDB, you can see that there is one more test1 user in the database, and the data can also be found by executing the query statement, which is consistent with the result found above in mysql.

I tried to migrate another database with a large amount of data. If there is a large amount of data in the imported mysql library, the data will not be found immediately because oracle has a statistical analysis mechanism.

Statistical information is mainly a kind of information that describes the size, scale and data distribution of tables and indexes in the database. For example, the number of rows in the table, the number of blocks, the average size of each row, the leaf blocks of the index, the number of rows in the index field, the size of different values, and so on, are all statistics. Based on these statistical data, CBO calculates the cost of various plans under different access paths and different join methods, and finally selects the plan with the lowest cost.

You can collect system statistics manually using the dbms_stats package that comes with oracle. DBMS_STATS package, mainly provides the collection (gather), delete (delete), export (export), import (import), modify (set) statistical information methods. The sentences I use here are as follows:

Exec dbms_stats.delete_schema_stats ('TEST1'); exec dbms_stats.gather_schema_stats (ownname = >' TEST1',degree = > 4 cascade = > true)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.