PHP Migrating data from Oracle to MySQL Data migration Instance _php tutorial

why migrate?

First of all, from the operational cost considerations, with MySQL can save a lot of money. On the other hand, the stability and function of MySQL are constantly improved and enhanced, which can basically meet the needs of customers, such as support multi-node deployment, data partitioning and so on. And MySQL is easier to use than Oracle. This is why customers are required to migrate existing Oracle data tables and content to MySQL.

Why do you write your own scripts?
The migrated tables and data are pretty much, with hundreds of tables. Therefore, manual completion is not very convenient. Also tried some free migration tools, such as: Mysqlmigrationtool, and so on, found that the transfer of the field type is not very good (perhaps the original Oracle table design is not very well), will lead to incomplete data, feel not too reliable, so decided to write the migration script to rest assured that some, Any non-conforming requirements can be adjusted immediately, so let's start.

Technical support Used

1.php5

2.php Oci8

3.mysql 5.1

Migrating table Structures

Get schema All tables

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 MySQL for table structure creation and data migration work.

Get all fields and types for 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 allows you to know the name of the table field, the type, the length, whether it is allowed to be empty, and the default value. Because Oracle is not fully compatible with MySQL field types, you need 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

Get primary Foreign unique key for a single table the primary Foreign unique key for a single table can be obtained from Oracle using the following statement

Cc. column_name and C.CONSTRAINT_NAME=CC. Constraint_name and C.OWNER=CC. OWNER and C.TABLE_NAME=CC. table_name ORDER by C.constraint_type, C.constraint_name, CC. POSITION < key="" 取得表的所有foreign="" ;为="" 取得表的所有unique="" 取得表的所有primary="" 为="">

Get the index of a single table

Use the following statement to get the index of a single table from Oracle

T.table_name = UPPER (' {$table _name} ')

This allows you to know the index name of the table, the field being indexed.

Assemble all table information into MySQL table structure with table name, field information, primary key, Foreign, Unique. Index and other information, you can set up the table structure. Since Oracle does not have a self-increment field, a workaround is to use primary key as the self-increment field in MySQL, because there is no way to accurately know which field is using SEQ for self-increment in Oracle.

Migrating views

Get all views of the schema. Use the following statement to get all views of the schema from Oracle

SELECT View_name,text from user_views

This gives you the name of the view and the constituent statement that creates the view.

Oracle incompatible with MySQL view processing

Oracle's build statements for view are somewhat looser, and MySQL constructs a statement that is strictly for view, from which it cannot be constructed from a subquery, that is, that the human shape statement is not allowed.

CREATE VIEW ' view_name ' as SELECT * FROM (SELECT * FROM table_name) ...

So, one way to deal with such a view is to set up the subquery into a view and replace the subquery with the newly created view name.

Migrating data

Data migration, from the Oracle database select data, assembled into the MySQL INSERT statement can be. Two points to note: One is that Oracle's date type data format is inconsistent with the MySQL date type format, so use To_char (field_name, ' yyyy-mm-dd ') to convert it to the appropriate data format for MySQL; The second is that the contents of the Clob field in Oracle are used Oci_fetch_array ($stid, oci_assoc| oci_return_nulls| Oci_return_lobs) to remove it and insert it into the MySQL text field. When MySQL inserts a lot of data, also note that in window My.ini or my.cnf default max_allowed_packet is 1M to change it to

[Mysqld]...max_allowed_packet = 16M ...

To avoid the error of MySQL goes away, the Linux version does not have this problem because its default value is 16M.

Content that cannot be migrated

Triggers, stored procedures in Oracle are not the same as in MySQL, so it is not possible to automatically migrate the past through a scripting program. To be overridden manually.

Summarize

From Oracle to MySQL data migration, table structure and data is basically able to translate past, view some is not fully compatible with Oracle statement, so you want to make some intermediate table. Triggers and stored procedures cannot be panned, and these two parts need to be rewritten. Table structure after the translation of the past, to see if it is more appropriate, if it is not very good can also be adjusted by alter and other statements. When importing large amounts of data, it is important to note the Max_allowed_packet values in the MySQL configuration file (My.ini or MY.CNF), which should be set too small to avoid the destabilizing factors such as error or failure to import data or garbled characters.

http://www.bkjia.com/PHPjc/446655.html www.bkjia.com true http://www.bkjia.com/PHPjc/446655.html techarticle why migrate? First of all, from the operational cost considerations, with MySQL can save a lot of money. On the other hand, the stability and function of MySQL constantly improve and enhance, basically can meet the customer ...

