Migration from ORACLE to MySQL - CLOB
I need to migrate about a dozen tables from ORACLE 10g to MySQL 5. I have manually migrated the schema to MySQL. I am able to write SQL*PLUS queries to extract ORACLE data into insert statements (including date conversions to MySQL format etc) that I can run against the MySQL database. The CLOB fields are tripping me because of single quotes, double quotes and carriage returns in the data. I can possibly change all single quotes to two single quotes and double quotes to two double quotes and MySQL will be happy ingesting that data into TEXT fields. The carriage returns are breaking the lines when the SQL*PLUS data is spooled into a flat file. How can I deal with quotes and carriage returns in CLOB data? Thanks, Rajesh
JOIN migration from Oracle to MySQL
Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), ... - doesnt' work. Exits a solution for this example? Other syntax possibilities? regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
[EMAIL PROTECTED] wrote: Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), The exact error message would be helpful, but I'm seeing at least two problems: 1) you're aliasing two tables as 'bl'. The aliases need to be unique. 2) The second ON clause shouldn't start with AND. Otherwise you should have no problem doing this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
Hello, thank you - now it works. d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1), d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2) I had to put the alias to all listet fields in the select. regards, Spiker Original-Nachricht Datum: Fri, 31 Aug 2007 09:30:13 -0400 Von: Baron Schwartz [EMAIL PROTECTED] An: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Betreff: Re: JOIN migration from Oracle to MySQL [EMAIL PROTECTED] wrote: Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), The exact error message would be helpful, but I'm seeing at least two problems: 1) you're aliasing two tables as 'bl'. The aliases need to be unique. 2) The second ON clause shouldn't start with AND. Otherwise you should have no problem doing this. -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
[EMAIL PROTECTED] wrote: Hello, thank you - now it works. d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1), d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2) I had to put the alias to all listet fields in the select. Unless you are relating PT1 to PT2 in some way, you should not expect this query to perform well because you will be generating a Cartesian product between PT1 and PT2. I doubt this is actually what you are trying to do (although it will eventually work). If you posted just a few more details about the query you are trying to write, we could try to help you to rewrite it in a way that will perform much better than the translation you just attempted. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
LOL - an entertaining read! Entertaining? I feel to see the humor in his post. I thought it was concise and well written, with an undertone of I know I'm swearing in church but So yes, I found it entertaining (I agree that it was not necessarily humorous or funny). Ah right :-) ... now I understand. One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. While your factual observations are undoubtedly correct, the conclusions bear some discussion. In particular for data warehousing constraints are not so important - as the ETL process that loads your data typically needs to check it anyway - and are often not practical - for instance enabling a foreign key constraint on a 10 billion row/10TB fact table is gonna just take too long ...(you tend to see ALTER TABLE ADD CONTRAINT xxx ... DERERRED/NOVERIFY or similar syntax with other database vendors to add the constraint but stop it doing anything except being a data point for the optimizer). Data warehousing always requires a slightly different strategy, I agree. When it comes to database application, I'm always talking about online transaction processing and the like. I agree that all the Mysql storage engines need work ... I assume that's being sorted (perhaps not as fast as we all would like) by the various developers. And just be be clear, the storage engines of most databases need work - for instance I work for a company that has used Postgres to make a parallel shared nothing data warehouse engine (sounds a bit like NDB huh?), and yep, the Postgres storage engine has areas we are wanting to improve! I don't consider the different storage engines in MySQL a strong point because none of them do the complete works. Now, if, for example, Falcon gets finished and it does full text indexing, transactions, check/unique/primary and foreign key constraints, then we're getting somewhere. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective Grant, LOL - an entertaining read! Entertaining? I feel to see the humor in his post. One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Martijn Tonies wrote: LOL - an entertaining read! Entertaining? I feel to see the humor in his post. I thought it was concise and well written, with an undertone of I know I'm swearing in church but So yes, I found it entertaining (I agree that it was not necessarily humorous or funny). One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. While your factual observations are undoubtedly correct, the conclusions bear some discussion. In particular for data warehousing constraints are not so important - as the ETL process that loads your data typically needs to check it anyway - and are often not practical - for instance enabling a foreign key constraint on a 10 billion row/10TB fact table is gonna just take too long ...(you tend to see ALTER TABLE ADD CONTRAINT xxx ... DERERRED/NOVERIFY or similar syntax with other database vendors to add the constraint but stop it doing anything except being a data point for the optimizer). I agree that all the Mysql storage engines need work ... I assume that's being sorted (perhaps not as fast as we all would like) by the various developers. And just be be clear, the storage engines of most databases need work - for instance I work for a company that has used Postgres to make a parallel shared nothing data warehouse engine (sounds a bit like NDB huh?), and yep, the Postgres storage engine has areas we are wanting to improve! Cheers Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Grant Allen wrote: Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective Grant, LOL - an entertaining read! One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. Is is hard - maybe impossible - to design one storage that engine does *everything* well (e.g Oracle is not that good for very large data warehouses, as any Teradata sales bloke will tell you...), so I think this is a useful feature unique to Mysql. Cheers Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
On Thursday 26 July 2007 Rajesh Mehrotra's cat, walking on the keyboard, wrote: Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Please note that PostgreSQL provides a gateway to other databases thanks to the DBI-Link extension, as well as it support more than only jdbc-4 driver (of course the 4 is the best). Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Tangirala, Srikalyan wrote: Hi All: Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Sure, let's play devil's advocate for a minute. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective - Fast connections Nope, not unique. Prespawned connections in Oracle are about as fast as it gets for any db, short of using a cached connection. - Easy replication We'll, if by unique and not offered by Oracle you mean you get to experience the MySQL pain of sync'ing the data to start with by any one of numerous half-baked manual methods, sure. Silly old Oracle totally automates that, even giving you several handy GUI or sql options depending on your preference. MySQL definitely wins on the does half the job criteria. - Overall ease of use Easy for who? You're absolutely right for simple installs; a quick db to support a simple web page; the persistence layer for a million and one open source apps that could have chosen any db (mysql, postgres, sqllite, jet, bdb, isam, you name it). But have you ever tried to reorg your physical storage in MySQL with the system online? Get the optimiser to do something intelligent with subselects? Handle transaction semantics across storage engines? Easy isn't the word that springs to mind. (OK, I'm all suited up with the asbestos ... flame away :-) ). Ciao Fuzzy :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
All well and good but that comparison is dated: March 14, 2005 Many enhancements all around since then. Simon -Original Message- From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:32 AM To: Tangirala, Srikalyan; cluster Cc: mysql@lists.mysql.com Subject: RE: Migration from Oracle to MySQL Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Cluster Mailing List For list archives: http://lists.mysql.com/cluster To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from Oracle to MySQL
Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Hi, Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? I thought it was your study? MySQL doesn't have (compared to Oracle): - check constraints - a procedural language as mature as Oracle PL/SQL - triggers on a per statement basis as Oracle - Java Stored Procedures or the ability to use .NET - Synonyms - Schemas - Type, Typed Tables, Object Tables, Object Types etc... - integrated PL/SQL Debugger interface Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
Thanks for your input Raj. Regards, Sri -Original Message- From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:32 AM To: Tangirala, Srikalyan; cluster Cc: mysql@lists.mysql.com Subject: RE: Migration from Oracle to MySQL Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from ORACLE 9i to MySQL
Shawn, others, Maybe the US Air Force has an unlimited budget but the rest of us do not. It seems to me that they powers that be in Nguyen's shop have made a decision (rational or not, you know how some managers are) to move away from a PREMIUM-priced package like 9i to something that can perform comparably to 9i but at a small fraction of the cost. Calling it an 8th grade toy makes you sound uninformed of what MySQL is really capable of. Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Besides, most of those fancy things in the premium databases can be duplicated or nearly duplicated using very little client-side code. Of the things that cannot be run in client-side code (I am particularly thinking of stored procedures and triggers) those are coming in 5.0.x. Do you think NASA, Yahoo, and a host of other Fortune 100 companies made a mistake by using MySQL in their production enviroments? I don't. It all depends on the application it's used for. MySQL 5 is a very nice release - once stable, of course - but in some regards, it has a long way to go. No doubt, many Oracle applications can be converted to MySQL, but this is because those applications don't use Oracle well enough :) IMO, duplicating something that can, could and should be done on the server in client code is a step backwards. In earlier days, the foreign key constraints like described in the MySQL documentation was a shining example of ignorance on the part of the documentation writers. Luckily, InnoDB has foreign key constraints. But there are plenty of other applications that cannot be converted to MySQL, no doubt, some run fine and dandy... We use MySQL here in the office as well, but use InterBase and Firebird for others... The right tool for the job makes the whole difference. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from ORACLE 9i to MySQL
Shawn Green, You are right? I agree as you said Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Thank you for input, Nguyen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 2:25 PM To: Johnson, Michael Cc: mysql@lists.mysql.com; 'Nguyen, Phong' Subject: RE: Migration from ORACLE 9i to MySQL Johnson, Michael [EMAIL PROTECTED] wrote on 07/28/2005 01:56:33 PM: Why are you going backwards MySql is an 8th grade toy. -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 9:42 AM To: mysql@lists.mysql.com Subject: Migration from ORACLE 9i to MySQL I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Maybe the US Air Force has an unlimited budget but the rest of us do not. It seems to me that they powers that be in Nguyen's shop have made a decision (rational or not, you know how some managers are) to move away from a PREMIUM-priced package like 9i to something that can perform comparably to 9i but at a small fraction of the cost. Calling it an 8th grade toy makes you sound uninformed of what MySQL is really capable of. Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Besides, most of those fancy things in the premium databases can be duplicated or nearly duplicated using very little client-side code. Of the things that cannot be run in client-side code (I am particularly thinking of stored procedures and triggers) those are coming in 5.0.x. Do you think NASA, Yahoo, and a host of other Fortune 100 companies made a mistake by using MySQL in their production enviroments? I don't. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Migration from ORACLE 9i to MySQL
Thank you for your input, V/R, Phong -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, July 29, 2005 3:41 AM To: Johnson, Michael ; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; 'Nguyen, Phong' Subject: Re: Migration from ORACLE 9i to MySQL Shawn, others, Maybe the US Air Force has an unlimited budget but the rest of us do not. It seems to me that they powers that be in Nguyen's shop have made a decision (rational or not, you know how some managers are) to move away from a PREMIUM-priced package like 9i to something that can perform comparably to 9i but at a small fraction of the cost. Calling it an 8th grade toy makes you sound uninformed of what MySQL is really capable of. Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Besides, most of those fancy things in the premium databases can be duplicated or nearly duplicated using very little client-side code. Of the things that cannot be run in client-side code (I am particularly thinking of stored procedures and triggers) those are coming in 5.0.x. Do you think NASA, Yahoo, and a host of other Fortune 100 companies made a mistake by using MySQL in their production enviroments? I don't. It all depends on the application it's used for. MySQL 5 is a very nice release - once stable, of course - but in some regards, it has a long way to go. No doubt, many Oracle applications can be converted to MySQL, but this is because those applications don't use Oracle well enough :) IMO, duplicating something that can, could and should be done on the server in client code is a step backwards. In earlier days, the foreign key constraints like described in the MySQL documentation was a shining example of ignorance on the part of the documentation writers. Luckily, InnoDB has foreign key constraints. But there are plenty of other applications that cannot be converted to MySQL, no doubt, some run fine and dandy... We use MySQL here in the office as well, but use InterBase and Firebird for others... The right tool for the job makes the whole difference. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from ORACLE 9i to MySQL
from a purely religous logical architecture viewpoint it is better to keep the business rules as close to the persistence layer (ex. RDBMS) as possible. in the practical physical/business world it is severly hyperlinearly expensive (both hardware as well as Oracle licenses) to support that model. put another way: Oracle is an insanely expensive application server but my wife's in sales there so as long as people keep writing those seven figure checks I'm not going to complain too much... :-) personally, I've been very impressed w/MySQL so far. we use it to run several sections of our site that run tens of millions of queries per day. MySQL replication has also been orders of magnitude more stable than AQ ever was. we have been replicating about 16GB/day across 12 nodes for months without the slightest hiccup. just don't ask me to do SQL/Server! when an RDBMS is responsible for the biggest DOS in the history of the internet that should tell you something... there, if we're going to flame let's at least pick a target we cal all agree on... :) It all depends on the application it's used for. MySQL 5 is a very nice release - once stable, of course - but in some regards, it has a long way to go. No doubt, many Oracle applications can be converted to MySQL, but this is because those applications don't use Oracle well enough :) IMO, duplicating something that can, could and should be done on the server in client code is a step backwards. In earlier days, the foreign key constraints like described in the MySQL documentation was a shining example of ignorance on the part of the documentation writers. Luckily, InnoDB has foreign key constraints. But there are plenty of other applications that cannot be converted to MySQL, no doubt, some run fine and dandy... We use MySQL here in the office as well, but use InterBase and Firebird for others... The right tool for the job makes the whole difference. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from ORACLE 9i to MySQL
I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from ORACLE 9i to MySQL
Why are you going backwards MySql is an 8th grade toy. -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 9:42 AM To: mysql@lists.mysql.com Subject: Migration from ORACLE 9i to MySQL I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from ORACLE 9i to MySQL
Johnson, Michael [EMAIL PROTECTED] wrote on 07/28/2005 01:56:33 PM: Why are you going backwards MySql is an 8th grade toy. -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 9:42 AM To: mysql@lists.mysql.com Subject: Migration from ORACLE 9i to MySQL I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Maybe the US Air Force has an unlimited budget but the rest of us do not. It seems to me that they powers that be in Nguyen's shop have made a decision (rational or not, you know how some managers are) to move away from a PREMIUM-priced package like 9i to something that can perform comparably to 9i but at a small fraction of the cost. Calling it an 8th grade toy makes you sound uninformed of what MySQL is really capable of. Sure MySQL may have a few fewer bells and whistles than Oracle but if you don't need to rely on all of the gee-whiz and just need fast, stable data storage and retrieval, MySQL is an excellent choice. Besides, most of those fancy things in the premium databases can be duplicated or nearly duplicated using very little client-side code. Of the things that cannot be run in client-side code (I am particularly thinking of stored procedures and triggers) those are coming in 5.0.x. Do you think NASA, Yahoo, and a host of other Fortune 100 companies made a mistake by using MySQL in their production enviroments? I don't. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Migration from ORACLE 9i to MySQL
I got no experience in migrating Oracle to Mysql, but here might be what you were looking for: http://dev.mysql.com/downloads/migration-toolkit/1.0.html Scott On 7/28/05, Nguyen, Phong [EMAIL PROTECTED] wrote: I will be migrating Oracle database 9i to Mysql. Do anyone have any experience in doing this?. Please share with us! Thank you very much, V/R, Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Power to people, Linux is here.
Re: Migration from ORACLE 9i to MySQL
Johnson, Michael wrote: MySql is an 8th grade toy. So why are you here? Go haunt an Oracle mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle
Its a small migration. -Satish Stefan Hinz wrote: Dear Satish, Thank you for your help. Your welcome. I used MyODBC 3.51, and successfully migrated to MySQL. Is this a MySQL user stories case, or just a small migration? Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Satish Vohra [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 12:27 PM Subject: Re: Migration from Oracle Dear Stefan, Thank you for your help. I used MyODBC 3.51, and successfully migrated to MySQL. Regards Satish Stefan Hinz, iConnect (Berlin) wrote: Dear Satish, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. (1) I am not familiar with Oracle, but I assume (oh, that word again) that it has a dump utility to create SQL files (with CREATE TABLE / INSERT statements). If it has, you can dump table definitions + data out of Oracle and import them into MySQL like that (using the MySQL Monitor command line client): shell mysql dump_from_oracle.sql In this case, it's fairly easy (and fast), but you will have to make sure the Oracle column types (DECIMAL, NUMERIC, INT, CHAR etc.) in use are being supported by MySQL. (2) Another way is to create the table definitions manually (choke!), export the data from Oracle (SELECT ... INTO OUTFILE) and import them into MySQL using LOAD DATA INFILE. (3) Yet another way is an ODBC connection from Oracle to MySQL, if Oracle supports ODBC. ODBC is slow, but not as desperately slow as I sometimes hear. As reported before on this list, I did a 1.2 million records import a couple of days ago from MS Access into MySQL using MyODBC 3.51.04. Table size was 500 MBytes, and it took 4 minutes on a 2 GHz machine running Win2K (table create definition and import of all data). HTH, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Satish Vohra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 5:12 PM Subject: Migration from Oracle Hi All, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. Regards Satish - 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 - 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
Re: Migration from Oracle
Dear Satish, Thank you for your help. Your welcome. I used MyODBC 3.51, and successfully migrated to MySQL. Is this a MySQL user stories case, or just a small migration? Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Satish Vohra [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 12:27 PM Subject: Re: Migration from Oracle Dear Stefan, Thank you for your help. I used MyODBC 3.51, and successfully migrated to MySQL. Regards Satish Stefan Hinz, iConnect (Berlin) wrote: Dear Satish, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. (1) I am not familiar with Oracle, but I assume (oh, that word again) that it has a dump utility to create SQL files (with CREATE TABLE / INSERT statements). If it has, you can dump table definitions + data out of Oracle and import them into MySQL like that (using the MySQL Monitor command line client): shell mysql dump_from_oracle.sql In this case, it's fairly easy (and fast), but you will have to make sure the Oracle column types (DECIMAL, NUMERIC, INT, CHAR etc.) in use are being supported by MySQL. (2) Another way is to create the table definitions manually (choke!), export the data from Oracle (SELECT ... INTO OUTFILE) and import them into MySQL using LOAD DATA INFILE. (3) Yet another way is an ODBC connection from Oracle to MySQL, if Oracle supports ODBC. ODBC is slow, but not as desperately slow as I sometimes hear. As reported before on this list, I did a 1.2 million records import a couple of days ago from MS Access into MySQL using MyODBC 3.51.04. Table size was 500 MBytes, and it took 4 minutes on a 2 GHz machine running Win2K (table create definition and import of all data). HTH, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Satish Vohra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 5:12 PM Subject: Migration from Oracle Hi All, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. Regards Satish - 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
Migration from Oracle
Hi All, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. Regards Satish - 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
Re: Migration from Oracle
Dear Satish, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. (1) I am not familiar with Oracle, but I assume (oh, that word again) that it has a dump utility to create SQL files (with CREATE TABLE / INSERT statements). If it has, you can dump table definitions + data out of Oracle and import them into MySQL like that (using the MySQL Monitor command line client): shell mysql dump_from_oracle.sql In this case, it's fairly easy (and fast), but you will have to make sure the Oracle column types (DECIMAL, NUMERIC, INT, CHAR etc.) in use are being supported by MySQL. (2) Another way is to create the table definitions manually (choke!), export the data from Oracle (SELECT ... INTO OUTFILE) and import them into MySQL using LOAD DATA INFILE. (3) Yet another way is an ODBC connection from Oracle to MySQL, if Oracle supports ODBC. ODBC is slow, but not as desperately slow as I sometimes hear. As reported before on this list, I did a 1.2 million records import a couple of days ago from MS Access into MySQL using MyODBC 3.51.04. Table size was 500 MBytes, and it took 4 minutes on a 2 GHz machine running Win2K (table create definition and import of all data). HTH, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Satish Vohra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 5:12 PM Subject: Migration from Oracle Hi All, I want to migrate from Oracle to MySQL. So I need to import the tables and data from Oracle. Can anyone suggest any tool which can do this. Any pointer regarding this will be appreciated. Regards Satish - 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