Log in to Oracle Database:

Use SQL*Plus or another Oracle client to connect to your Oracle database with the necessary privileges.

Prepare Data for Export:

• Identify the data you want to move.
• Perform any necessary cleanup or transformation on the data.
• Ensure there are no active transactions on the data you want to export.

Export Data to a CSV File:

Use the spool command in SQL*Plus to export the data to a CSV file. Here’s an example command to export data from a table named my_table:

‍

SET HEADING OFF

SET FEEDBACK OFF

SET COLSEP ","

SET PAGESIZE 0

SET LINESIZE <appropriate_value>

SPOOL my_table.csv

SELECT * FROM my_table;

SPOOL OFF

‍

Adjust the LINESIZE to an appropriate value that fits your data. The COLSEP is set to a comma here, but you can change it to another character if your data contains commas.

Log in to MySQL Database:

Use the MySQL command-line tool or another MySQL client to connect to your MySQL database with the necessary privileges.

Create Database and Table Structure:

Create a new database if needed.

Create the table(s) that will hold the imported data. Make sure the structure (columns and data types) matches the Oracle data you’re importing.

CREATE DATABASE my_mysql_db;

USE my_mysql_db;

CREATE TABLE my_table (

    column1 datatype,

    column2 datatype,

    ...

);

Adjust MySQL Settings:

If you’re importing a large amount of data, you may need to adjust MySQL settings, such as max_allowed_packet and bulk_insert_buffer_size, to optimize the import process.

Transfer the CSV File:

Use a secure method to transfer the exported CSV file from the Oracle server to the MySQL server, such as scp for Unix/Linux systems or WinSCP for Windows.

Import Data into MySQL:

Use the LOAD DATA INFILE command to import the CSV file into the MySQL table. For example:

‍

LOAD DATA INFILE '/path/to/my_table.csv'

INTO TABLE my_table

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 LINES;

Adjust the path and options as needed for your specific CSV format and file location.

Check the Imported Data:

Perform queries on the MySQL table to ensure that the data has been imported correctly.

Data Integrity Check:

• Compare row counts between the Oracle table and the MySQL table.
• Check for any data truncation or data type mismatches.
• If you have unique identifiers, consider writing a script to compare a subset of the data between the two databases to ensure consistency.

Remove Temporary Files:

Delete the CSV file and any other temporary files created during the export/import process to free up space and maintain security.

Adjust Database Settings:

If you changed any MySQL settings specifically for the import, consider resetting them back to their original values.

Optimize MySQL Tables:

After a large data import, it’s often a good idea to optimize the table using the OPTIMIZE TABLE command to improve performance.

Review Application Compatibility:

If the data is being used by an application, ensure that the application is compatible with MySQL and adjust any database connection settings or queries as needed.

Backup the MySQL Database:

After a successful migration, perform a backup of the MySQL database to ensure you have a copy of the newly imported data.

‍

This guide covers the basic steps for moving data from an Oracle database to a MySQL database without third-party tools. However, depending on the complexity and size of your data, additional steps and considerations may be necessary. Always perform a test migration first and have a rollback plan in place before proceeding with a production migration.