This discussion is archived
1 Reply Latest reply: Jul 25, 2011 5:33 PM by GaryT-Oracle RSS

SQL Server to Oracle migration queries

user11972128 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points