PHP moves data from Oracle to MySQL data migration instance

Why do you want to migrate?

First from the operating cost considerations, with MySQL can save a lot of money. On the other hand, MySQL stability and function constantly improve and enhance, basically can meet customer needs, such as support multi-node deployment, data partitioning and so on. And MySQL is easy to use, easier than Oracle. So the customer requires the existing Oracle data table and content to migrate to MySQL.

Why do you write your own script?

The tables and data migrated are quite numerous, with hundreds of tables. So it's not convenient to do it manually. Also tried some free migration tools, such as: Mysqlmigrationtool, and so on, found that the transfer of the field type is not very satisfied with the requirements (may be the original Oracle table design is not very good), will result in incomplete data, feel not too reliable, so decided to write their own migration script relieved some, There are not meet the requirements can be adjusted immediately, so start it.

The technical support that is used

1.php5

2.php Oci8

3.mysql 5.1

Migrate table structure

Get all tables of schema

Use the following statement to get all the table names in the schema from Oracle

SELECT　table_name　FROM　user_tables

You can then traverse all tables to create a table structure and migrate data to MySQL.

Get all the fields and types of a single table

Use the following statement to get all the fields and types of a single table from Oracle

SELECT　COLUMN_NAME,　DATA_TYPE,　DATA_LENGTH,　NULLABLE,　DATA_DEFAULT

FROM　USER_TAB_COLUMNS　　　WHERE　TABLE_NAME　=　UPPER('{$table_name}')　ORDER　BY　 column_id　ASC

This lets you know the name, type, length of the table field, whether it is allowed to be empty, and the default value. Because Oracle is not fully compatible with MySQL's field type, it is necessary to establish a corresponding relational table for the field type.

Oracle	Mysql
Number (<11) >	Int
Number (>11)	bigint
varchar	varchar
VARCHAR2 (<255) >	varchar
VARCHAR2 (>255)	Text
Clob	Text
Date	Date