Re: Transfer data from MySQL to Oracle

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 18 Apr 2003 04:22:24 GMT
Message-ID: <3E9F817A.1080109@adelphia.net>

Bing Du Test wrote:
> 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?

ORACLE's DATE type stores the timestamp in an internal format. You decide how it appears when you SELECT that field. It looks like the MySQL format you have is year/month/day/hours/minutes/seconds. On a SELECT query, you tell oralce that's the format you want

SELECT to_char(TAMUBPPSTUDENTLASTSEEN, 'YYYYMMDDHHMISS')

   FROM lastseen;

This would affect how you insert/update the date data also

UPDATE lastseen
  set TAMUBPPSTUDENTLASTSEEN = to_date('20030326072839', 'YYYYMMDDHHMISS')   where ssn = '987654321' ;

internally Oracle Dates are numeric and record down to a fraction of a second.

>
>

>>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?

For a load this small, performance really isn't an issue. And your tables map one-to-one on the fields, so I'd use the SQL*Loader. It's just a two step process:

Step ONE

from MySQL modify any settings for line layout, field separators, etc and any settings needed to redirect the result to a file and run the query: SELECT * from lastseen;

Step TWO

Build a SQL*Loader control file to handle the layout. run the loader with that layout and the file from step one.

Step THREE

Done

>
>

>>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?

I haven't played with that option actually. The probably do. Try to do a GOOGLE search on this group for some discussion of it in the last few months.

>
> Bing
>
>

Thanks for the easy questions!

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Thu Apr 17 2003 - 23:22:24 CDT