1 Reply Latest reply: Jul 25, 2011 7:33 PM by GaryT-Oracle RSS

    SQL Server to Oracle migration queries

    user11972128
      Hello Everyone,

      We are migrating a bunch of SQL Server 2000 database to Oracle 11g using SQL Developer. We are having the following questions on the migration tool. Appreciate if you could provide some inputs based on

      * Is it possible to modify the schema name (schema that is created in oracle) from the tool or does it have to be done by generating the script and modifying it.
      * Is it possible to specify oracle tablespace name under which the schema needs to be created.
      * IS there any way to filter SQL Server system tables from getting migrated.

      Thanks.
        • 1. Re: SQL Server to Oracle migration queries
          GaryT-Oracle
          SQL Developer requires a migration repository to migrate a third-party database to an Oracle database. To use an Oracle database for the migration repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to create a user named MIGRATIONS, create a database connection to that user, and use that connection for the migration repository; and if you wish, you can later delete the MIGRATIONS user to remove all traces of the migration from the database.

          When you create a user for migrations, specify the tablespace information as in the following example, instead of using the defaults for tablespaces:
          CREATE USER migrations IDENTIFIED BY password
          DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp,
          Do not use a standard account (for example, SYSTEM) for migration

          So essentially you create an Oracle user whose schema is to be used as the destination for the objects to be migrated,
          or use an existing Oracle user and schema.

          http://download.oracle.com/docs/cd/E18464_01/doc.30/e17472/toc.htm
          Oracle® SQL Developer User's Guide
          Release 3.0
          Part Number E17472-05

          There is also SQL Server specific documentation
          http://download.oracle.com/docs/cd/E18464_01/doc.30/e18462/toc.htm
          Oracle® SQL Developer Supplementary Information for Microsoft SQL Server and Sybase Adaptive Server Migrations
          Release 3.0
          Part Number E18462-03