Migrating To Oracle Using Custom Object Names
Sybase , SQL Server and other databases allow for long identifier names.
Oracle allows for a maximum of 30 Bytes when naming objects like users, tables, ...
When it comes to migrating objects to Oracle, SQL Developer will truncate the object names and resolve clashes with unique names.
Say for example you have two tables called
Oracle SQL Developer will convert these to
Oracle allows for a maximum of 30 Bytes when naming objects like users, tables, ...
When it comes to migrating objects to Oracle, SQL Developer will truncate the object names and resolve clashes with unique names.
Say for example you have two tables called
- Application_Name_SubArea_Name_SpecificAreaName_Table_Table1
- Application_Name_SubArea_Name_SpecificAreaName_Table_Table2
Oracle SQL Developer will convert these to
- Application_Name_SubArea_Name_S
- Application_Name_SubArea_Name_1
This default new name may not be to your liking, so SQL Developer has an easy way of changing this mapping.
First perform the Capture and Convert phases. You do not need to perform all the phases of a migration as the migration wizard allows you to stop and start a migration at many points. In this case click "Finish" on the Convert page of the migration wizard.
Once the capture and convert is complete, we will have a list of all the objects to be migrated including their original "source" name and their default "target" name. But we have yet to translate SQL objects like procedures and views. This is important...
Left click on the "Converted Database Objects" node in the migration navigator and choose the "Object" report tab. This report allows you to change the "target" name of any of the objects. It also allows you to sort and filter the objects by types. Once you have made and committed the changes you can proceed with the migration.
Right click on the "Converted Database Objects" node and choose "Translate".
This will pop you back into the migration wizard at the correct page so you can continue the migration.
The neat thing about this is, now the Translation will take into account you chosen target names.
Choosing a specific Target user is a common requirement.
For example: By default bugtestcase2.dbo is mapped to bugtestcase. But you many have an existing user you want to use. You can use the same "Objects" report to change the target user.
You should also choose "Offline" option during the generation and data move. This is so you can provide the existing password of your existing users.
You'll end up with a CREATE USER in your generation script. But you can comment this out or ignore the error. Just be sure to give the existing password when prompted for it.