Small data transfer script - from Oracle to MySQL
This simple script converts data from an Oracle database to a MySQL database :
- List all tables using a connection to the Oracle database
- Maps data types to their MySQL equivalent
- Create and populate data to a MySQL connection
- Database requirements:
- Requires a connection to an Oracle Database (via oracle-db npm package)
- Requires a connection to a MySQL Database (via mysql npm package)
- Reads data from Oracle and writes it to MySQL using SQL commands
- Data type conversion:
- VARCHAR2 => VARCHAR
- DATE => DATE
- CLOB => LONGTEXT
- BLOB => LONGBLOB
- NUMBER => INTEGER or DECIMAL(...)
- Multiple command line parameters to adapt migration for your needs
git clone https://github.com/e-dot/oracle2mysql.git
cd oracle2mysql
cd setup
npm install
SET NODE_PATH=...\oracle2mysql\setup\node_modules
SET NODE_ORACLEDB_USER=... [DEFAULT:system]
SET NODE_ORACLEDB_PASSWORD=... [DEFAULT:empty]
SET NODE_ORACLEDB_CONNECTIONSTRING=... [DEFAULT:localhost/orcl]
cd ..
node bin/oracle2mysql.js [options]
- -list_request : define the Oracle query to list all SQL tables for migration (defaults to
SELECT * FROM dba_tables ORDER BY OWNER, TABLE_NAME
)- -list_request "SELECT * FROM dba_tables WHERE OWNER = 'mylogin' ORDER BY TABLE_NAME" : process only tables owned by 'mylogin'
- -list_request "SELECT * FROM dba_tables WHERE OWNER = 'mylogin' AND TABLE_NAME='mytable'" : process only table 'mytable' owned by 'mylogin'
- -schema_map : define the schema mapping from Oracle (owner) to MySQL (database) ; defaults to ":" (maps owner to database as-is)
- -schema_map "*:MYBASE" : replace owner by 'mybase' (e.g. table OWNER.NAME is migrated into MYBASE.NAME)
- -timeout : define the query timeout, in seconds (defaults to 600 s i.e. 10 minutes)
- -nodrop : if set, no "DROP TABLE" will be executed during migration (you can pre-fill tables with specific values, if needed)
- -nocreate : if set, no "CREATE TABLE" will be executed during migration (you have to manually create tables first)
- -truncate : if set, a SQL command "TRUNCATE TABLE" will be execute during migration of every table (emptying the table)
- -engine: if set, all "CREATE TABLE" commands will use this ENGINE (defaults to "MyISAM")
- -step: define the number of rows browsed and copied on every loop (defaults to 1 000)
- -step 100000 : list 100 000 entries from source table at a time and create a single INSERT command into destination table (faster, but uses more resources)
-
Use option
--max_old_space_size
when launching node, e.g. :node --max_old_space_size=2048 bin/oracle2mysql.js [options]
-
Set environment variable ORA_SDTZ before running node, e.g. on Windows:
SET ORA_SDTZ=Europe/Paris
node --max_old_space_size=2048 bin/oracle2mysql.js [options]