Re: Transfer data from MySQL to Oracle

Hi,

Have a look at Oracle Migration Workbench or since you have just one table, use mysqldump to export the table to a text file. Then you can load the data to Oracle using SQL Loader.

Best regards, Dmitry

--
SQLWays - Database migration software for IBM DB2, Oracle, SQL Server,
Sybase and MySQL.
http://www.ispirer.com/products

"Bing Du Test" <bing-du_at_tamu.edu> wrote in message
news:3E9F21B1.B701AA2_at_tamu.edu...


> Ed Prochak wrote:

>

> > Bing Du Test wrote:

> > > What do people usually use to transfer a large number (e.g. 150,000)

> > > from one kind of database (e.g. MySQL) to another (e.g. Oracle)?

Thanks


> > > in advance for any ideas and pointers.

> > >

> > > Bing

> > >

> >

> > depends.  8^)

> >

> > How big is the source DB? Is that "large number" refering to the number

of


> > records? (in which case 150,000 is small) or the number of tables? (man

that's


> > BIG)

> >

>

> Thanks Ed.  Apparently our case falls into your small category.  One DB

that just


> has one table which has 150,000 records.  Can not be simpler :)?  The

following


> shows the layout of the table.

>

> mysql> desc lastseen;

>

+--------------------------+---------------+------+-----+---------+-------+


> | Field                    | Type          | Null | Key | Default | Extra

|


>

+--------------------------+---------------+------+-----+---------+-------+


> | ssn                      | char(12)      |      | PRI |         |

|


> | studentid                | char(16)      |      | MUL |         |

|


> | lastmodified             | timestamp(14) | YES  |     | NULL    |

|


> | tamuBPPStudentLastSeen   | timestamp(14) | YES  |     | NULL    |

|


> | tamuBPPFacultyLastSeen   | timestamp(14) | YES  |     | NULL    |

|


> | tamuBPPStaffLastSeen     | timestamp(14) | YES  |     | NULL    |

|


> | tamuSIMSEnrolledLastSeen | timestamp(14) | YES  |     | NULL    |

|


> | tamuSIMSAdmittedLastSeen | timestamp(14) | YES  |     | NULL    |

|


> | tamuOtherFeedLastSeen    | timestamp(14) | YES  |     | NULL    |

|


>

+--------------------------+---------------+------+-----+---------+-------+


> 9 rows in set (0.00 sec)

>

>

> >

> > how complex is the source data model? (many tables realtions?? will the

model


> > remain the same in Oracle??)

> >

>

> The table in Oracle that the data will be moved to has the exact same

table layout


> shown below:

>

> SQL> desc lastseen;

>  Name                            Null?    Type

>  ------------------------------- -------- ----

>  SSN                             NOT NULL CHAR(9)

>  STUDENTID                                CHAR(9)

>  TAMUBPPSTUDENTLASTSEEN                   DATE

>  TAMUBPPFACULTYLASTSEEN                   DATE

>  TAMUBPPSTAFFLASTSEEN                     DATE

>  TAMUSIMSENROLLEDLASTSEEN                 DATE

>  TAMUSIMSADMITTEDLASTSEEN                 DATE

>  TAMUOTHERFEEDLASTSEEN                    DATE

>

> How does 'DATE' look like in Oracle?  In MySQL, timestamp is like

20030326072839.


> If I want to keep the same format, is DATE the correct data type to be

used?  Does


> Oracle support this kind of time format at all?

>

> >

> > Some suggestions include

> >

> > brute force method: Dump the source DB to text files and load the Oracle

DB


> > with a combination of SQL*Loader, SQL and PL/SQL, remapping the data to

tables


> > as needed.

> >

> > Program method: use PERL and DBI to connect to both databases and move

the


> > data, performing any transformations needed along the way.

> >

>

> Since I've never done this before, but both sounds doable to me.  Just

wondering


> which one is faster?

>

> >

> > Oracle method: use the Heterogenous services to connect from Oracle to

the


> > MySQl DB and move the data, performing any transformations using SQL,

PL/SQL.


> >

>

> Heterogenous services sound pretty new to me.  Does using the Heterogenous

> services require Oracle DBA's intervention?

>

> Bing

>

>