Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to import the data from the original Oracle database into MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to import the data from the original Oracle database into MySQL.

In some cases, the data in Oracle needs to be imported into MySQL. How to import it easily and quickly? It can be easily done with MySQL Migration Toolkit in MySQL GUI Tools, and the tool also has very good support for data of CLOB and BLOB types.

1. Run MySQL Migration Toolkit, all the way from "Next" to "Source Database", select Oracle Database Server in Database System, and if you use it for the first time, you will be told to load the driver ojdbc14.jar.

After loading the driver, the interface will look like this, fill in the connection information for the Oracle database, and press "Next" to continue.

2. In Target Database, the default Database System is MySQL Server, fill in the connection information of the corresponding MySQL database in Connection Parameters, and press "Next" to continue.

3, carry on the database connection test, press "Next" to Source Schemata Selection after passing the test, click the Schemata that is ready for data migration, and press "Next" to continue.

4. In Object Type Selection, click the Detailed selection button, select the table that is not migrated in the list on the left below, and put it in the list on the right, that is, all the remaining tables in the list on the left will be migrated. After selecting, press "Next" to continue.

5. In the Migration of type Oracle Schema of Object Mapping, if you want to set parameters, click the Set Parameter button. Select Multilanguage; if the default database table is UTF8. If the default database table is GBK, you need to select User defined and fill in charset=gbk, collation=gbk_general_ci below. The parameter point Set Parameter button should be set in Migration of type Oracle Table. Select Data consistency/multilanguage; if the default database table is UTF8. If the default database table is GBK, you need to select User defined and fill in addAutoincrement=yes, charset=gbk, collation=gbk_general_ci, engine=INNODB below. After selecting, press "Next" to continue.

6. Then go to Manual Editing, where you can modify the table creation script. Due to the differences in syntax rules between Oracle and MySQL, it is usually necessary to adjust the data type and default value of the script. For example, the default value sysdate is usually set for data of type Timestamp in Oracle, but it is not recognized in MySQL. Select Show All Objects in Filter, then select the table in Migrated Objects to modify the script, and then click Advanced on the lower left to edit the script. After the modification, click the Apply Changes button on the right to save and press "Next" to continue.

7. Then "Next" all the way, and if there is no error, you can import the data from the specified table into MySQL. If there is an error message, you need to go back to Manual Editing and modify the script again.

It is important to add that when conducting large volume data, especially CLOB data, an exception may occur: "Packets larger than max_allowed_packet are not allowed". This is because the MySQL database has a system parameter max_allowed_packet with a default value of 1048576 (1m), which can be queried in the database with the following statement:

Show VARIABLES like max_allowed_packet%

You can modify this parameter by finding the my.ini file in the folder and adding a line in the my.ini file [mysqld]:

Max_allowed_packet=16777216

Restart MySQL so that you can import data not larger than 16m, of course, this value can be adjusted as needed.

At this point, I believe you have a deeper understanding of "how to import the data from the original Oracle database into MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.