One, foreword

This document describes the entire process of migrating table data from MySQL database to Oracle database using Navicat, version 12.0.11

Operating environment and tools:
  1. mysql5.7
  2. oracle18c
  3. windows
  4. Navicat12.0.11
  5. idea

Two, start the transplant

Go to Tools -> Data Transfer

The source
The target

A capital

Tips: If the field name and table name for lower case, oracle operation data will appear when you can’t find the table or view the errors, the solution is to must be enclosed in double quotation marks to query, so that when we through program operation data must be enclosed in double quotation marks, which greatly increased after the migration database workload, so need to check the conversion object called the capital here, If the oracle keyword appears in the field name during the conversion process, it will automatically give us double quotes to solve the keyword problem!! 【 ex: user -> “user” number -> “number” desc -> “level” level

Select the table that needs to be transplanted. Here I selected all of them in one shuttle

Tips: There may be some unsuccessful tables in the transmission process, it is good to guide manually ~~

Three, problem,

1. Resolve the problem that oracle automatically adds a primary key

There are several ways for Oracle to set autoincrement primary keys:
  1. The sequence + The trigger
  2. The sequence + Hibernate configuration(Note: This method only applies to connecting to the database through Hibernate)
  3. Add column syntax since oracle12cGENERATED BY DEFAULT AS IDENTITY
Solution:

Added in the DDL to create table SQL command on the primary key, to create a table structure, and then a separate import data from the oracle Here due to the small make up oracle version of 18 c so when creating a table to add on the primary key grammar can complete!

1. Back up data > Data pump mode

Data pump -> Data pump export

② View DDL in idea as follows

③ DDL file content replacement auto-increment primary key tool class

Tips: The time type in the small series database is DATE and needs to be changed to TIMESTAMP. This problem can be directly modified by using the replacement function of IDEA. Other modifications can be made according to the actual situation of individuals

public class MySQLToOracleTest {

    public static void main(String[] args) {
        try {
            replaceDDLContent("D: \ \ Users \ \ zq \ \ Desktop \ \ test \ oracle \ oracle_ddl TXT"); // TODO is the location where your DDL files are stored
        } catch(IOException e) { e.printStackTrace(); }}/** * replaces the increment primary key ID ** in the text file@param path
     * @throws IOException
     */
    public static void replaceDDLContent(String path) throws IOException {
        // The original content
        String srcStr = "not null";
        // What to replace
        String replaceStr = "GENERATED BY DEFAULT AS IDENTITY";
        / / read
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        / / replace
        String line = null;
        // The line to replace
        Map<Integer, Object> lineMap = new HashMap<>(104);
        // Define the order variable
        int count = 0;
        while((line = bufIn.readLine()) ! =null) {
            count++;
            if (line.contains("create table")) {
                lineMap.put(count + 2, line);
            }

            // Iterate over the map keys
            for (Integer key : lineMap.keySet()) {
                if(count == key && ! line.contains("NVARCHAR2")) {
                    // TODO is hereline = line.replaceAll(srcStr, replaceStr); lineMap.put(count, line); }}// Write the line to memory
            tempStream.write(line);
            // Add a newline character
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close the input stream
        bufIn.close();
        // Write the stream in memory to a file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
        System.out.println("File Location :"+ path); System.out.println(lineMap); }}Copy the code
4. Copy the replaced DDL to a new console in idea to create a table

Select all DDL and click on upper left corner to create table.

5. Import backup data

Data Pump -> Data pump import

⑥ Finally check the data import success!

SQL > alter table ID = 1; SQL > alter table ID = 1; SQL > alter table ID = 1;

2. The primary key ID cannot be incremented from the maximum value of the table ID

Join out to modify the table from the start of the ID increment SQL can!
SELECT
	'SELECT ''ALTER TABLE SEWAGE_GY.' || t1.table_name || ' MODIFY(' || t1.Column_Name || ' Generated as Identity (START WITH '' || MAX( ' || t1.Column_Name || '+1 ) || ''));'' FROM ' || t1.table_name || ' UNION ALL' AS FINAL_SQL
FROM cols t1
LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
WHERE
	NOT EXISTS (
        SELECT t4.Object_Name
        FROM User_objects t4
        WHERE
            t4.Object_Type = 'TABLE'
            AND t4.TEMPORARY = 'Y'
            AND t4.Object_Name = t1.Table_Name
	)
	AND t1.IDENTITY_COLUMN = 'YES'
ORDER BY t1.Table_Name, t1.Column_ID
Copy the code

Command parsing:

ALTER TABLE ID = 10000; ALTER TABLE ID = 10000; MODIFY(primary key ID Generated as Identity (START WITH 10000)); SELECT table_name FROM user_tables; SELECT * FROM cols t1 LEFT JOIN user_col_comments T2 ON t1.table_name = SELECT * FROM cols t1 LEFT JOIN user_col_comments T2 ON t1.table_name t2.Table_name AND t1.Column_Name = t2.Column_Name LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4 WHERE t4.Object_Type = 'TABLE' AND t4.TEMPORARY = 'Y' AND T1. Object_Name = t1.Table_Name) AND t1.Table_Name = 'Table_Name' AND t1.IDENTITY_COLUMN = 'YES' ORDER BY t1.Table_Name, SELECT t1.table_name,t1.Column_Name FROM cols t1 LEFT JOIN user_col_comments T2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4 WHERE t4.Object_Type = 'TABLE' AND t4.TEMPORARY = 'Y' AND t4.Object_Name = t1.Table_Name ) AND t1.IDENTITY_COLUMN = 'YES' ORDER BY t1.Table_Name, t1.Column_IDCopy the code

UNION ALL

3. SQL statement conversion in the program

Here combined with personal language practical operation…