Re: Transfer data from MySQL to Oracle

From: Bing Du Test <bing-du_at_tamu.edu>
Date: Thu, 17 Apr 2003 16:50:41 -0500
Message-ID: <3E9F21B1.B701AA2@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 Received on Thu Apr 17 2003 - 16:50:41 CDT