Home > Documentation > Oracle to MySQL

As soon as Oracle to MySQL migration is completed it is very important to thoroughly validate the resulting database. It includes validation of the following database entries:

• Table definitions
• Data
• Indexes
• Foreign keys
• Views

Table Definitions

To explore table definitions both Oracle and MySQL uses statement DESC table_name. Correct migration of Oracle table definition means that each column in the target MySQL table has appropriate type, size and other attributes. This article specifies proper conversion for all Oracle types into MySQL.

Data

The simplest way to check that all data has been migrated correctly is to compare number of rows in Oracle and MySQL tables. Both DBMS use the following query to get number of rows in table:

SELECT COUNT(*) FROM table_name

Converted data can be checked by visual comparison of particular fragments in both Oracle and MySQL tables. In order to get more comprehensive diagnostics, the data can be exported in CSV format and compared by standard text processing tools like FC (file compare).

If table contains one or more BLOB columns, it is reasonable to compare max volume of BLOB data in Oracle and MySQL tables to make sure that data was not truncated. Both DBMS use the following query for this purpose:

SELECT length(blob_column_name) FROM table_name

Indexes

Conversion of indexes can be validated by verifying number of indexes, indexed columns and attributes for each index. Oracle allows to list indexes via this query:

SELECT * FROM all_indexes WHERE table_name = 'table name'

In MySQL run the following query to display information about indexes:

SHOW INDEX FROM `table name`

Foreign Keys

Verification of foreign keys is similar to indexes. Oracle allows to extract foreign keys through this query:

SELECT 
    a.table_name,a.constraint_name,a.delete_rule,b.column_name 
FROM 
    user_constraints a, user_cons_columns b 
WHERE 
    a.constraint_name=b.constraint_name and a.constraint_type='R' 

MySQL displays information about foreign keys belong to the particular table at the bottom of the following query output:

SHOW CREATE TABLE `table name`;

Views

To validate migration of views and queries from Oracle to MySQL it is required to compare each converted SELECT-statement and the original with respect to differences between SQL dialects of source and target DBMS. Oracle allows to list all available views using the query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

Then each view can be explored in Oracle database like this:

SELECT text FROM all_views WHERE view_name='{name of view}'

MySQL provides the query below for the same purpose:

SELECT table_name, view_definition 
  FROM  information_schema.views 
WHERE table_schema='database name';

Main differences between syntax of Oracle and MySQL queries are listed in this article.