After migration from Oracle to Azure SQL Database

Question

Please excuse my ignorance but I am not a DBA and until my now database experience has been relegated to basic CRUD operations. I just used SSMA to migrate an Oracle database to an Azure SQL Server instance and have some questions

Q1. Every SQL Server database I have ever used has the dbo schema. This one contains the name of the Oracle schema it came from. So now instead of tables or procs being dbo.<table name or proc name> it is SDCS.<table name or proc name>.

Am I supposed to change all of these to dbo or just leave them as they are?

Q2. All of my procs are now in this format: SDCS.INSERT_BMA$INSERT_SDCS_PRIME and has several things such as SDCS.INSERT_SDCS$SSMA_Initialize_Package

Am I supposed to change the name to <schema>.INSERT_BMA and then just fix any errors within the procs?

Q3. I have a ton of procs related to Oracle such as ssma_oracle.db_clean_storage, ssma_oracle.db_check_init-packages, ssma_oracle.db_error_exact_once_row_check, etc.

What, if anything am I supposed to do with these?

Answer 1

Hello,

Q1. Every SQL Server database I have ever used has the dbo schema. This one contains the name of the Oracle schema it came from. So now instead of tables or procs being dbo.<table name or proc name> it is SDCS.<table name or proc name>.

>

Am I supposed to change all of these to dbo or just leave them as they are?

This is configurable in SSMA, by default we map Oracle's schema to SQL Server schema. It depends on how you want your database to be accessed. If you migrate multiple Oracle schemas to one SQL Server database, it makes sense to map them to separate schemas (which is the default).

Q2. All of my procs are now in this format: SDCS.INSERT_BMA$INSERT_SDCS_PRIME and has several things such as SDCS.INSERT_SDCS$SSMA_Initialize_Package

>

Am I supposed to change the name to <schema>.INSERT_BMA and then just fix any errors within the procs?

These are packaged procedures. SQL Server does not have a concept of packages, so during conversion the names of these procedures get prepended with the package name to eliminate the name collision with procedures defined outside of the package.
There is no configuration related to this, this is how SSMA does the conversion for the reason outlined above. If you want you can change the names and update hte references after the conversion.

Special ..$SSMA_Initialize_Package procedure is added to initialize packaged variables values with the defaults. Again, there is no concept of packages (and therefore packaged variables) in SQL Server, so SSMA emulates them using special db_storage table.

Q3. I have a ton of procs related to Oracle such as ssma_oracle.db_clean_storage, ssma_oracle.db_check_init-packages, ssma_oracle.db_error_exact_once_row_check, etc.

>

What, if anything am I supposed to do with these?

In a continuation of the previous answer - these procedures and functions support the emulation of some of the Oracle's behavior related to packages and they will be needed at runtime when you use your database.

In addition to the routines you have listed, there are also emulation procedures and functions that directly map to the Oracle's procedures and functions that are not available in SQL Server natively or behave differently. For example, Oracle's SUBSTR function allows negative position, while SQL Server's SUBSTRING does not. This difference in behavior is covered by the provided a set of [ssma_oracle].[substr*] emulation functions.

Hope this helps,
Alex.