The Oracle SQL developer is not only a great tool for SQL development, with its migration workbench, it is an excellent tool for database migration. Did I also mention it is free?
To get it started, you will need to download the latest version of the tool which is version 1.5.1. Also it is recommended to install the latest updates. The current release of update as of 6/15 is 220.127.116.11.40.
Next, you will need to create a repository. An database account, which has CREATE SESSION, RESOURCE, and CREATE VIEW must be created first. Then logon into SQL Developer as that account. From the tool, create a repository from Migration menu > Repository Management > Create Repository. This process creates a bunch of MD* and MIGR* tables and packages.
To connect to other non-Oracle databases, you will need to create connections through provided JDBC drivers. If somehow, your SQL Developer does not show these 3-rd party connection tabs, you will have to configure it. Instruction is clearly documented on Oracle site here.
In my case, I migrate one of our legacy applications on MS SQL to Oracle. Unfortunately this application contains column name named “rowid”, which is Oracle’s reserved word, in most of tables. The tool has a mechanism to append a suffix “_” (with no quotes) if it encounters these reserved words. So “rowid” will be converted to “rowid_”. To see the list of all reserved words, you can just query “KEYWORD” column in MIGRATION_RESERVEDW_WORDS table under the repository schema. Unfortunately, the “rowid” is not really in this table, I have to insert this manually. I added a keyword “privilege” as well because, again, there is a column named “privilege” used in application.
insert into migration_reserved_words values ('rowid');
insert into migration_reserved_words values ('privilege');
I encountered a bug (which Turloch O’Tierney pointed out – thanks) when using version 1.5.1 without the latest patch. In the function “first_char_check” under MIGRATION_TRANSFER package, a number “4” is missing. The impact of this is that If the column name is numeric prefix, for example, 3Q2008, a letter “A” will be prefixed into it to be “A3Q2008”. But due to this bug, it won’t do it with, for example, 4Q2008, resulting in a failure when trying to create table. With the latest patch, it seems like it was fixed already because I now see number 4.
v_allowed := C_DISALLOWED_CHARS || '012356789_$';
The process of migration is straight-forward. You will start with capture then convert. You can capture and convert the whole database at once. But I would not recommend that because if error is encountered, it might be difficult to troubleshoot.
You can just capture only the table first, this will create a converted script. Before running it, please inspect it first and comment un-needed sections especially CREATE default USER syntax and CONNECT using default user. By running the converted script, it will create the definitions of tables, sequences, triggers and constraints. If there is no error, then you can run to move data.
Once data is there, repeat the steps with stored procedures and views. Obviously, you will just need to run the converted script – there is no need for data movement.