Hi The Oracle export function, exp, does not export data in a clean file format, and whilst it may be possible to tidy the output to load into another database, you'd do better writing a perl script to dump the tables required to disk.
The time factors in moving data from db1 on one machine to db2 on another are problematic. The options are endless. The best option is to produce a file and use the LOAD INFILE method. This is the quickest way to get data into MySQL. Inserting individual rows will take to long. Simon On Wednesday 20 Nov 2002 11:00 pm, Andrew Braithwaite wrote: > Wesley, > > Forgive me because I'm not overly familiar with Oracle, and this is an > unproven idea.... > > If you can run the oracle export function from unix shell, you could do > something like the following.... > > -- Install the mysql client on the oracle db server (will be quick and > won't need any downtime) > > -- grant permissions on the new mysql server to allow the newly installed > remote client access to it > > -- make sure the mysql client on the oracle server can write to the new > mysql db ok > > -- create all the required tables on the new mysql server's db (leaving out > indicies if you want to gain performance) > > -- In a script (using variables and loops), run something like the > following from shell on the oracle server (where "t" is the table name): > > [shell]# Oraclexportutility --tabdelim t > t; mysqlimport multimaps t;sleep > 5;rm -f t > > (where "Oraclexportutility --tabdelim t" is the oracle export widget that I > know nothing about!!) > > I know that the same method works with: > > [shell]# mysql -B -N --execute="select * from t" multimaps > t; mysqlimport > multimaps t;sleep 5;rm -f t > > If you can find a way to pipe the output straight to the mysql import > utility without writing a copy to disk then that's even better! (I drew a > blank at this one..) Also, if your tables are small enough to use a > ramdisk for the temp table "t" then that's good too. > > You could run several of these occurences at once (although it would hammer > your disks) to make the most of the bandwith available. > > There are lots of options to mysqlimport such as: > -C, --compress Use compression in server/client protocol. > -f, --force Continue even if we get an sql-error. > -i, --ignore If duplicate unique key was found, keep old row. > -r, --replace If duplicate unique key was found, replace old row. > > And (I quote) If one uses sockets to connect to the MySQL server, the > server will open and > read the text file directly. > > As I said, these are ideas & I hope it will at least give you some ideas > too... > > Cheers, > > Andrew > > > > > -----Original Message----- > From: Wesley Brown [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 20 November 2002 20:24 > To: Simon Windsor; [EMAIL PROTECTED] > Subject: RE: The dreaded move from Oracle to MySQL > > > Well, it looks like Simon has the best possible answer so far. I did a test > dump into a delimited file from Oracle and imported that using the LOAD > command in mysql and sustaned about 11,000 inserts a second. This is with > no indexes on the mysql tables at all. I have also tried using the dbtools > import function but it will ether time out or just hang on the big tables. > > --Simon Says---- > To do this I would do the following, > > - on Oracle server > - - Create a NFS device > - - Create a FIFO pipe, using mknod > - - export data from Oracle to FIFO pipe > > - on MySQL server > - - Mount remote NFS device > - - Use MySQL Load command to load data, or write your own loader in perl > - - when load is finished, build the MySQL indexes > > Can you explain this procedure to me a little better or point me to > something I can read? I still have to convince the person who manages the > Oracle box to open up an NFS share. Another way we could increase load > times would be to use 4 scripts to load data from 4 tables at once. We have > the bandwith to handle that and the MySQL box seems to be handling request > just fine at 11,000 inserts its still not tapping out both procs or killing > the disk I/O. > > Thanks to everyone who responded so quickly! > > Cheers, > Wes > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Simon Windsor mail: [EMAIL PROTECTED] tel: 01454 617689 mob: 07720 447385 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
