First you should use SQL Developer 4.0.2 if you are not already. Its available for free on OTN
There are two ways to convert tables from MySQL to Oracle.
1) Copy To Oracle
2) Migration To Oracle.
Copy to Oracle is the simplest method. It does not involve creating a migration repository. But it only migrates tables and data with no constraints or defaults or indexes. The data set to move should be reasonably small as well as it is being moved over JDBC.
It may suit your purpose of trying out Oracle.
Migrate to Oracle requires setting up a migration repository. But it does migrate constraints , defaults and indexes and can also provide Offline Data Move scripts which can handle the movement of large data sets.
Heres a small script to create a new user MIGRATIONREPO and grant it the correct privileges.
CREATE USER MIGRATIONREPO IDENTIFIED BY oracle;
ALTER USER MIGRATIONREPO QUOTA UNLIMITED ON USERS;
GRANT CONNECT,RESOURCE,CREATE VIEW ,CREATE MATERIALIZED VIEW to MIGRATIONREPO;
Then using SQL Developer, create a connection to MIGRATIONREPO, right click on that connection and choose "Associate Migration Repository".
Hopefully you have better success creating the migration repository with theses privileges.
Migration Wizard On Step 8 : Target Database
Choose Offline Mode.
This will present you with a generation script which you can inspect before running.
The generation script should be run using SQL Developer worksheet and choosing a sufficiently privileged user/connection in Oracle typically SYSTEM.
Note, If you'd rather work with the repository you currently have you can browse your Migration navigator.
Browse to Converted Database Objects, Right Click and choose "Generate Target".
This will open the Migration Wizard at step 8 again. Allowing you to choose Offline Mode so that you can inspect the generation script.
Hope this helps,
SQL Developer Team.
Hello Dermot, thanks for your help on this.
I tried all your suggestions and succeeded.
First of all the "Copy To Oracle" Function works like a charm since I could just select all tables and copy the data to oracle. That's great.
Second,the "Migrate to Oracle" works fine, when I use the offline mode. I was able to migrate all data to oracle and i could login with the newly created user.
I deleted the user(with cascade) and started the same migration again (same settings) but with online mode. As described in my first post, migration "succeeded" but I couldn't login and there was no user created when I check with system account.
So I can migrate or copy stuff to oracle and test my application.
Still there's the question why it is not working in online mode.
Thanks for your help,