When you migrate your database from Oracle to MySQL, this comes with a lot of benefits. This is because Oracle is more useful for the construction of complex projects in companies that handle heavy database systems. Some of the key benefits of the migration include:
- Open-source ability
- Low cost of ownership
- Tight integration with web
- A bigger circle of MySQL administrators
Apply for data science institutes in hyderabad to know more.
The process of migration, like other DBMS, follows a number of steps which are:
- Export definition of Oracle tables into data definition language (DDL) ‘CREATE TABLE’ statements
- Convert those DDL-statements into MySQL format and load into target server
- Export Oracle data into corresponding CSV files
- According to target database, modify CSV files and import into MySQL database
- Export views, stored procedures, and triggers into SQL statements
- According to MySQL syntax, load statements and code into the target database
The database administrator in charge of these steps must be fully aware of the intricacies of the process especially the best method to automate the process of migration. An example of an Oracle client application that is used to test all queries and statements is SQL*Plus. But other client applications can be used as much. The command line for connecting SQL*Plus to database is:
sqlplus username/password@database
Check out data science course in hyderabad to know more.
Table definitions
There is need to convert Oracle table definitions into MySQL format after extracting them as DDL statements as:
SQL> select table_name from user_tables;
Each Oracle table’s definition is then extracted thus:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
Before the resulting script is loaded into MySQL, it must be corrected as such:
- Remove all keywords that are specific to Oracle and do not have MySQL equivalent from the end of CREATE TABLE statements (starting from “USING INDEX PCTFREE…”)
- Replace double quotes symbol used by Oracle to enclose identifier names by MySQL equivalent (`)
- According to a certain table that can be found online, the data types are converted into equivalents of MySQL.
Data
An intermediate storage called Comma Separated Values (CSV) files receives data from migration to MySQL from Oracle. There is a sequence of command which guides the import of Oracle data into CSV format:
SQL> set heading off
SQL> spool filename.csv
SQL> select column1 || ‘,’ || column2 || … from mytable;
SQL> set colsep ‘,’
SQL> select * from my_table;
SQL> spool off;
The LOAD DATA statement is then used to load the CSV files into the MySQL database:
LOAD DATA LOCAL INFILE ‘a_table.csv’
INTO TABLE a_table
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’;
Indexes and constraints
This query is used in this step:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
And these statements allow to get the definition of particular index:
select VIEW_NAME, TEXT from SYS.USER_VIEWS SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Views
Use the following query to get list of all views from Oracle database in form of CREATE VIEW statements:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
In creating views, both Oracle and MySQL have similar syntax of statements but not physically identical. This is the reason it is always necessary to process the statement before it reaches the destination DBMS. Visit the data science institutes in Bangalore to get an admission now.
Since Oracle has several features which can be sued to create views but are unsupported by MySQL, these features should be expelled from the conversion process – default, force/no force, with check option, with object identifier, with read only, under, and XMLType views
Learn more about database migration from Oracle to MySQL at: https://www.convert-in.com/docs/ora2sql/intro.htm
