Migrate to Existing Oracle Users
Problem
By default, SQL Developer will migrate a Sybase, SQL Server ,... database to a brand new target user in Oracle. This new user is defined (CREATE USER ... ) at the start of the the generation script.
For Sybase and SQL Server, we append the owner name to the database name (ex: dbo_Northwind) , to come up with a new Oracle target user name. This is done to create the same separation of objects. At the moment (SQL Developer 3.1) we define the password to be the same as the user name. Its always best to perform the generation phase "offline" so you can inspect the generation script first before it is run.
Generation Script : Notice the CREATE USER ....
In the above example I migrated the SQL Server Northwind database with default preferences.
Default Preferences
Solution
If you wish to migrate to an existing Oracle User then there are two methods
- Least Privilege Schema Migration : Run the generation script directly into the chosen connection, with no users created
- Object Naming: Specify what target user name you want
Least Privilege Schema Migration
Tools>Preferences>Migration>Generation Options
Choose Least Privilege Schema Migration.
Once checked, you can reconvert a Captured Database Objects model. As long as there is only one target Oracle user , then the generation script will be created, without any CREATE USER or CONNECT ...
The script will then create the migrated objects into the target connection specified.
Note that if the migration project has more than one target user (ex: dbo_Northwind & user2_Northwind) SQL Developer will not create a least privilege script. It will create a standard script with CREATE USER, CONNECT TO .. for each user.
Object Naming: Specify what target user name you want
If you have more than one existing target Oracle user and you want the objects to be migrated into them. Then you have to explicitly state what Oracle user you want to use. SQL Developer needs to know what user to connect to when creating the objects. SQL Developer also needs to know the target user names when converting Views, Triggers, Procedures and Functions. So that cross database references are resolved correctly.
Any object name change, including user/schema name change, should be performed in the convert page of the Migration Wizard.
To see the Object Naming tab, you must capture the source database first. Then perform the convert. Otherwise SQL Developer does not know the name of the source objects.
Just choose "Proceed to Summary Page" on the Capture page of the Migration Wizard
You can right click on the Captured Database Objects node in the Migration Projects navigator and choose Convert.
Object Naming Tab : Only visible after the source database has been captured.
You can filter by object type ,to show just the Users/Schemas.
SRC_TYPE='MD_SCHEMAS'
Change the New Identifier to your target Oracle user. And Commit the change
The generation script will now use the new target name. And all cross database references will be correct.
Note that you may want to comment out the CREATE USER as it will fail if the user already exists, or you can just ignore the error.
Note you should review the CONNECT statements to make sure the existing password is used, possibly prompt for it using substitution.
Note that using the above two methods allows SQL Developer to perform the data move correctly as the target users are identified.