6 Replies Latest reply: Jul 1, 2014 2:28 PM by ljames RSS

    MySQL to oracle migration

    74b6c15d-b026-4743-8d1d-eea1c0d04532

      Hi all, I've a MySQL dump of about 6 gb please help me to migrate it to oracle step by step.

      IM in using oracle 10g in rhel.

      THanks in advance.

        • 1. Re: MySQL to oracle migration
          Zoltan Kecskemethy

          See instructions here: MySQL Migration

          (using Oracle SQL Developer)...

          • 2. Re: MySQL to oracle migration
            begaz01

            Zoltan,

             

            Thanks for the link.  I was also looking for a step by step guide to migrate from mysql to Oracle.  All worked great for the migration, except for the case sensibility for column names.  The column names in MySQL are all lower case and in Oracle are upper case.  I manually renamed all columns to lower case names, but the application code still sees them as uppercase names.  This discrepancy is causing the application to fail.  Any Ideas?  I read in a couple of blogs something about changing this parameter: lower_case_table_names  to 2 on the mysql server.  Please advice.

             

            MySQL: Linux 2.6.32-431.11.2.el6.centos.plus.x86_64 #1 SMP Tue Mar 25 21:36:54 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

            Oracle 11G: 11.2.0.4.2 SunOS 5.10 Generic_150401-11 i86pc i386 i86pc

             

            Thanks in advance,

             

            Jonathan

            • 3. Re: MySQL to oracle migration
              ljames

              Johnathan

               

              Let us consider that you migrate a table called address from MySql to Oracle with the following structure.

               

              SQL> desc address

              Name                                      Null?    Type

              ----------------------------------------- -------- ----------------------------

              ADDRESSID                                 NOT NULL NUMBER(10)

              ADDRESSLINE1                              NOT NULL VARCHAR2(60 CHAR)

              ADDRESSLINE2                                       VARCHAR2(60 CHAR)

              CITY                                      NOT NULL VARCHAR2(30 CHAR)

              STATEPROVINCEID                           NOT NULL NUMBER(10)

              POSTALCODE                                NOT NULL VARCHAR2(15 CHAR)

              SPATIALLOCATION                                    BLOB

              ROWGUID                                   NOT NULL CHAR(36 CHAR)

              MODIFIEDDATE                              NOT NULL DATE

               

              you can query the data as follows.

               

              SQL> select ADDRESSID,CITY from address where rownum < 2;   <== Upper case column

               

              ADDRESSID CITY

              ---------- ------------------------------

                     194 Sammamish

               

              SQL> select addressid,city from address where rownum < 2; <= Lower case

               

              ADDRESSID CITY

              ---------- ------------------------------

                     194 Sammamish

               

              it is not case sensitive.  But if you query the data dictionary as follows

               

              SQL> select column_name from user_tab_columns where column_name = 'addressid';

               

              no rows selected

               

              SQL>  select column_name from user_tab_columns where column_name = UPPER('addressid');

               

              COLUMN_NAME

              ------------------------------

              ADDRESSID

               

              then "Yes".  You might want to change the application code to reflect the change.

               

              Let me know is this is your concern as per the case sensitiveness goes.

               

              Leo

              • 4. Re: MySQL to oracle migration
                begaz01

                Thanks for the reply Leo.  I'm still having trouble figuring this out.  Here is what I have:

                 

                1.  The migration was successful, but all tables and columns are now uppercase.  The application could not recognize any of the tables with errors like:

                      EXECUTE ON CONNECTION 3: SELECT * from sys_dictionary where lower("name") =

                      'sys_db_object'

                      Syntax Error or Access Rule Violation detected by database (ORA-00904:

                      "name": invalid identifier)

                 

                2.  I changed the columns to lowercase with: alter table TABLE_NAME rename column COLUMN_NAME to "column_name";

                 

                3.  Queried one of the application tables "SYS_DICTIONARY":

                     select distinct lower("name") from sys_dictionary;  => Works now from the command line.

                     select distinct "name" from sys_dictionary;  =>  Works

                     select distinct NAME from sys_dictionary;  => Does not work

                     select distinct name from sys_dictionary; => Does not work

                 

                4.  The actual data dictionary says:

                     SQL> select column_name from user_tab_columns where table_name like 'SYS_DICTIONARY' and column_name = 'name';

                 

                      COLUMN_NAME

                       ------------------------------

                       name

                 

                       SQL> select column_name from user_tab_columns where table_name like 'SYS_DICTIONARY' and column_name = UPPER('name');

                 

                       no rows selected

                 

                5.  Described the table:

                 

                     SQL> desc sys_dictionary

                     Name                                                                                                              Null?    Type

                     ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

                     name                                                                                                                       VARCHAR2(40 CHAR)

                     element                                                                                                                    VARCHAR2(30 CHAR)

                     choice                                                                                                                     NUMBER(10)

                     choice_table                                                                                                               VARCHAR2(40 CHAR)

                     choice_field                                                                                                               VARCHAR2(40 CHAR)

                     reference_key                                                                                                              VARCHAR2(40 CHAR)

                     reference                                                                                                                  VARCHAR2(40 CHAR)

                     table_reference                                                                                                            NUMBER(3)

                     element_reference                                                                                                          NUMBER(3)

                     reference_qual                                                                                                             VARCHAR2(255 CHAR)

                     reference_cascade_rule                                                                                                     VARCHAR2(20 CHAR)

                     reference_type                                                                                                             VARCHAR2(10 CHAR)

                     reference_floats                                                                                                           NUMBER(3)

                     dependent                                                                                                                  VARCHAR2(40 CHAR)

                     ....

                 

                It seems like I must always have the column name inside double quotes.  Is this correct?

                • 5. Re: MySQL to oracle migration
                  ljames

                  Johnathan,

                   

                  When you alter the table name it actually stores inside data dictionary as it is.

                   

                  SQL> desc sys_dictionary

                  Name                                      Null?    Type

                  ----------------------------------------- -------- ----------------------------

                  NAME                                               VARCHAR2(40)

                  ELEMENT                                            VARCHAR2(30)

                  CHOICE                                             NUMBER(10)

                  CHOICE_TABLE                                       VARCHAR2(40)

                  CHOICE_FIELD                                       VARCHAR2(40)

                  REFERENCE_KEY                                      VARCHAR2(40)

                  REFERENCE                                          VARCHAR2(40)

                  TABLE_REFERENCE                                    NUMBER(3)

                  ELEMENT_REFERENCE                                  NUMBER(3)

                  REFERENCE_QUAL                                     VARCHAR2(255)

                  REFERENCE_CASCADE_RULE                             VARCHAR2(20)

                  REFERENCE_TYPE                                     VARCHAR2(10)

                  REFERENCE_FLOATS                                   NUMBER(3)

                  DEPENDENT                                          VARCHAR2(40)

                   

                  SQL> alter table sys_dictionary rename column NAME to "name";

                   

                  Table altered.

                   

                   

                  SQL> desc sys_dictionary

                  Name                                      Null?    Type

                  ----------------------------------------- -------- ----------------------------

                  name                                               VARCHAR2(40)

                  ELEMENT                                            VARCHAR2(30)

                  CHOICE                                             NUMBER(10)

                  CHOICE_TABLE                                       VARCHAR2(40)

                  CHOICE_FIELD                                       VARCHAR2(40)

                  REFERENCE_KEY                                      VARCHAR2(40)

                  REFERENCE                                          VARCHAR2(40)

                  TABLE_REFERENCE                                    NUMBER(3)

                  ELEMENT_REFERENCE                                  NUMBER(3)

                  REFERENCE_QUAL                                     VARCHAR2(255)

                  REFERENCE_CASCADE_RULE                             VARCHAR2(20)

                  REFERENCE_TYPE                                     VARCHAR2(10)

                  REFERENCE_FLOATS                                   NUMBER(3)

                  DEPENDENT                                          VARCHAR2(40)

                   

                  So there is no "NAME" as column name. It is actually hard coded. It is 'name'.

                   

                  I dropped the table and recreated and try to the below.

                   

                  SQL> alter table sys_dictionary rename column NAME to name;

                  alter table sys_dictionary rename column NAME to name

                  *

                  ERROR at line 1:

                  ORA-00957: duplicate column name

                   

                   

                  SQL> alter table sys_dictionary rename column NAME to NAME;

                  alter table sys_dictionary rename column NAME to NAME

                  *

                  ERROR at line 1:

                  ORA-00957: duplicate column name

                   

                  I have to hardcode as

                   

                  alter table sys_dictionary rename column NAME to "name";

                   

                  At this point NAME and "name" is not same.

                   

                  So in order to work your queries you have to rewrite your queries as it is.

                   

                  SQL> select column_name from user_tab_columns where table_name like 'SYS_DICTIONARY' and column_name = 'name';

                   

                  COLUMN_NAME

                  ------------------------------

                  name

                   

                  SQL> select column_name from user_tab_columns where table_name like 'SYS_DICTIONARY' and upper(column_name) = 'NAME';

                   

                  COLUMN_NAME

                  ------------------------------

                  name

                   

                   

                  both the queries return value now. it is the matter where and how you use UPPER clause.

                   

                  Please write to me an email when you get a chance.

                  leo.james@oracle.com

                  • 6. Re: MySQL to oracle migration
                    ljames

                    Also see the below

                     

                    SQL> select distinct(lower(NAME)) from sys_dictionary;

                    select distinct(lower(NAME)) from sys_dictionary

                                          *

                    ERROR at line 1:

                    ORA-00904: "NAME": invalid identifier

                     

                    Reason :  NAME doesn't exist

                     

                     

                     

                    SQL> select distinct("name") from sys_dictionary;

                     

                    no rows selected

                     

                    Reason :  "name" do exist 

                     

                     

                    SQL>  select distinct(name) from sys_dictionary;

                    select distinct(name) from sys_dictionary

                                     *

                    ERROR at line 1:

                    ORA-00904: "NAME": invalid identifier

                     

                    Reason : name doesn't exist but "name"