Data migration and data transfer

Data migration and data transfer
1. Software Support 1.1 download migration tool: Navicat_Premium_11.0.101.2 connect to the database

1.2.1 open Navicat and click Connect. Create a MySQL connection and an oracle connection. The specific steps are as follows:

(Figure 1)

(Figure 2)

1.2.2 connect to the MySQL database:

(Figure 3)

 

2. Create a database in MySQL

2.1 Open the database link, right-click, and select create database:

(Figure 4)

2.2 fill in the table information:

(Figure 5)

3. migrate table structure

3.1 open the Oracle database and right-click to select data transmission:

 

(Figure 6)

3.2 fill in the general information of data transmission:

(Figure 7)

3.3 fill in advanced data transmission information:

(Figure 8)

3.4 after the table structure is migrated, several tables are randomly selected to check whether the table structure is correct.

4. Modify the table structure

4.1 After the migration is complete, open the Mysql database and execute the SQL script for modifying the table structure (note that it is best not to run the SQL file directly. Need to manually delete the foreign key ):

(Figure 9)

4.2 After the table structure is modified, several tables are randomly selected to check whether the table structure is correctly modified.

5. Modify the storage engine

5.1 After modifying the table structure, execute the SQL script for modifying the storage engine (Note: it is best not to directly run the SQL file ):

(Figure 10)

5.2 After the storage engine is modified, several tables are randomly selected to check whether the storage engine is correctly modified.

 

6. Import Data

* Do not import data to tables starting with mr in lejobdb. Otherwise, an error is reported (cause: the table has the same primary key)

6.1 open the Oracle database, right-click and select data transmission, as shown in figure 6)

6.2 fill in the data transmission general information: for example (Figure 7) Note: Data in a table with a foreign key cannot be imported.

6.3 fill in advanced data transmission information: Note: Use transactions when importing data

(Figure 11)

7. Check Data

Randomly extract tables from the exported database and check whether the data is correct.

Oracle data migration tool

SQL * Plus Copy command
Problem description
In SQL * Plus, how does one implement data replication between different tables, whether local or remote?
Problem Analysis
The copy command in SQL * Plus allows you to copy data between a remote database, a local database, or an Oracle database and a non-Oracle database. Its performance is the same as that of import/export.
Basic command format of copy:

Copy {from source_database | to destination_database} {append | create | insert | replace} destination_table [(column,...)] using <source_select_statement>

The database connection format is as follows:
Username/password \] @ connect_identifier
During data replication, the supported data types are char, date, long, number, and varchar2. .

The SQL * Plus Copy command can replicate data between different databases and between tables in different modes in the same database.
? • Copy data from a remote database to a local database.
? • Copy data from a local database (default) to a remote database.
? • Copy data from one remote database to another.
Generally, the copy command is used to copy data between an Oracle database and a non-Oracle database. If you copy data between Oracle databases, use the create table as and insert SQL commands.
• There are four types of control methods for the target table: replace, create, insert, and append.
? • The replace clause specifies the name of the table to be created. If the target table already exists, delete it and replace it with a table containing the copied data. If not, the target table is created.
? • Use the create clause to avoid overwriting existing tables. If the target table already exists, copy reports an error. If not, create the target table.
? • Insert data to an existing table.
Insert the queried rows to the target table. If the target table does not exist, copy returns an error. When insert is used, the using clause must select the corresponding columns for each column in the target table.
? • Append inserts the queried rows into the target table. If not, create the target table and insert it.
Answer
Note the following:

1) copy is an SQL * Plus command, not an SQL command. No extra points are required at the end of the statement;

2) because most copy commands are relatively long, the end of each line must have a hyphen (-) at the end of each line at the time of the branch, and the last line is not added.
The procedure is as follows.
Step 1: Use the using clause to specify a query, and copy the query result data to the employee table in the current mode of the local database. For example:

Copy from hr/hrd @ rensh-replace employee-using select last_name, salary-from emp_details_view-where department_id = 30

Step 2: Use create to copy data from a remote database to a local database.

Copy from hr/<your_password> @ bostondb-create empcopy-using select * from hr

Step 3: Copy data for other users.

Copy from hr/hr @ dbora-create job-using select * from renbs.jobs

Take user h ...... the remaining full text>

Oracle data migration tool

SQL * Plus Copy command
Problem description
In SQL * Plus, how does one implement data replication between different tables, whether local or remote?
Problem Analysis
The copy command in SQL * Plus allows you to copy data between a remote database, a local database, or an Oracle database and a non-Oracle database. Its performance is the same as that of import/export.
Basic command format of copy:

Copy {from source_database | to destination_database} {append | create | insert | replace} destination_table [(column,...)] using <source_select_statement>

The database connection format is as follows:
Username/password \] @ connect_identifier
During data replication, the supported data types are char, date, long, number, and varchar2. .

The SQL * Plus Copy command can replicate data between different databases and between tables in different modes in the same database.
? • Copy data from a remote database to a local database.
? • Copy data from a local database (default) to a remote database.
? • Copy data from one remote database to another.
Generally, the copy command is used to copy data between an Oracle database and a non-Oracle database. If you copy data between Oracle databases, use the create table as and insert SQL commands.
• There are four types of control methods for the target table: replace, create, insert, and append.
? • The replace clause specifies the name of the table to be created. If the target table already exists, delete it and replace it with a table containing the copied data. If not, the target table is created.
? • Use the create clause to avoid overwriting existing tables. If the target table already exists, copy reports an error. If not, create the target table.
? • Insert data to an existing table.
Insert the queried rows to the target table. If the target table does not exist, copy returns an error. When insert is used, the using clause must select the corresponding columns for each column in the target table.
? • Append inserts the queried rows into the target table. If not, create the target table and insert it.
Answer
Note the following:

1) copy is an SQL * Plus command, not an SQL command. No extra points are required at the end of the statement;

2) because most copy commands are relatively long, the end of each line must have a hyphen (-) at the end of each line at the time of the branch, and the last line is not added.
The procedure is as follows.
Step 1: Use the using clause to specify a query, and copy the query result data to the employee table in the current mode of the local database. For example:

Copy from hr/hrd @ rensh-replace employee-using select last_name, salary-from emp_details_view-where department_id = 30

Step 2: Use create to copy data from a remote database to a local database.

Copy from hr/<your_password> @ bostondb-create empcopy-using select * from hr

Step 3: Copy data for other users.

Copy from hr/hr @ dbora-create job-using select * from renbs.jobs

Take user h ...... the remaining full text>