1. Use Navicat Premium to connect oracle and mysql

To connect to local oracle, you need to download the corresponding oci file
OCI download address
Extract the downloaded oci file, Navicat premium - > tools - > Options, and find the extraction address of oci configuration oci file

2. Problems encountered in migration and Solutions

1. The date data type of Oracle needs to be converted to the timestamp type before migrating to mysql. When migrating, the tool will automatically convert the timestamp to the datetime data type in mysql.

Here is the reference
Since mysql's date type only supports storing dates and cannot store time, navicat will automatically convert oracle's date to mysql's date during conversion, which will lead to insufficient accuracy of mysql and error reporting. Therefore, it is necessary to convert oracle's date type to timestamp type in advance. If there are a large number of tables, manual modification is too cumbersome. I write a stored procedure to automatically convert the date type of all tables in oracle into timestamp type:

create or replace procedure convert_date_to_timestamp
/**************************
  function: Change the fields with DATE type in all tables in the database to TIMESTAMP type;
  The following types of tables need to be created:
  create table type_table_info (
    table_name varchar2(30),
    column_name varchar2(30),
    data_type varchar2(100)
  )  
***************************/
is
  v_query_base_sql varchar2(100) := 'SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = ''';
  v_query_table_sql varchar2(150);
  v_alter_sql varchar2(100);
 
  type table_type is table of type_table_info%rowtype;
  table_array table_type;
 
begin
DBMS_OUTPUT.ENABLE (buffer_size=>null);
  for c_tabs in (
       select table_name from user_tables where table_name <> 'type_table_info' 
    )
    loop
      v_query_table_sql := v_query_base_sql || c_tabs.table_name || '''';
      execute immediate v_query_table_sql bulk collect into table_array;
      for i in table_array.first .. table_array.last
        loop
          --DBMS_OUTPUT.put_line(table_array(i).column_name || ':' || table_array(i).data_type);
          if table_array(i).data_type = 'DATE' then
            v_alter_sql := 'alter table ' || table_array(i).table_name || ' modify ' || table_array(i).column_name || ' timestamp';
            DBMS_OUTPUT.put_line(table_array(i).column_name || ': ' || v_alter_sql);
            execute immediate v_alter_sql;
          end if;
          
        end loop;
    end loop;
end convert_date_to_timestamp;

Execution note:
(1) . create the table in the stored procedure first
(2) Compile the code in the plsql program window
(3) Call the stored procedure and report an error buffer problem. Dbms_output.enable (buffer_size = > null) has been added to the stored procedure; solve

begin
      convert_date_to_timestamp();
  end;

2. Max under MySQL_ allowed_ Packet parameter limit
(1) Command line modification (it can take effect without restart under liunx and windows)
mysql -u database name - p enter to modify

Here is the reference
set global max_allowed_packet = 210241024*10;
be careful:
If you modify it like this, an error will be reported: MySQL > set max_ allowed_ packet=16MB;
ERROR 1232 (42000): Incorrect argument type to variable 'max_allowed_packet'
Check whether the modification is successful

show VARIABLES like '%max_allowed_packet%';

3. Migration supports breakpoint migration. In consideration of efficiency, I used a machine to migrate part of it to the local sql database and export it as an sql file. During the migration from the springboard machine to the destination database, an error was reported. mysql import error was reported, indicating that super permission is required

Here is the reference
The user permission table is usually in the mysql database. Switch to the database
Authorized user super authority
update user set Super_priv = 'Y' where User = 'user name';
flush privileges;

3. Tool migration

Tools - > data transmission, select source library and destination library, and set relevant parameters in advanced to start migration

Reprinted from
https://blog.csdn.net/weixin_40946381/article/details/104003496
https://blog.csdn.net/dd276/article/details/52089216