Coming from MySQL, Going to Oracle: the Pitfalls

Question

All my development life I have only worked with MySQL for extended periods of time, and for a client we now need to work with an Oracle database for some performance testing and tuning.

Any obvious pitfalls in moving from working with MySQL to Oracle I should watch out for?

The things I discovered so far:

• There is only one database
• What MySQL calls a database is a Schema in Oracle
• A User and Schema is almost the same (still unclear of the differences here)
• There is no auto increment. Instead you need to create your own sequence
• Inserting multiple entries at the same time through multiple literal value tuples is not possible.
• Numeric formats are localized, which can cause headaches when importing from CSV files.

Other tips would be greatly appreciated. Any good resources/documented past experiences with the transition would also be welcome. Note that we're not actually migrating a database from one to the other, it is more about the adjustments I'd have to make personally in my way of thinking etc.

Answer 1

See this resources

Migrating MySQL to Oracle Part I

Migrating MySQL to Oracle Part 2

A great tool

Oracle Migration Workbench

Bye.

Answer 2

If you insert or update longer string than length of varchar2 column, Oracle will throw an exception. MySQL will silently truncate it. Better double check if your code doesn't (even inadvertently) depend on this behavior.