This discussion is archived
8 Replies Latest reply: Jan 25, 2013 10:58 AM by TurlochO'Tierney RSS

How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer

user480768 Newbie
Currently Being Moderated
Is there any way to migrate the views from MS SQL 2008 to Oracle 11g through SQL Developer? Please give me some detail steps. Thanks for your help.

Kevin
  • 1. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Kevin,

    Using SQL Developer:

    1/Simple
    Copy the tables with right click 'copy to Oracle' (does not migrate metadata such as foreign keys)
    Convert the view definition using the tools->migration->scratch editor.
    (Or create a table as select * from the view and 'copy to Oracle' that new table)

    2/More involved
    Right click on database 'migrate to oracle' takes you into a wizard - requires a repository for storing meta data. Will migrate the whole database. Additional information http://www.oracle.com/technetwork/products/migration/sqlserver-095136.html

    Turloch
    -SQLDeveloper Team
  • 3. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    user480768 Newbie
    Currently Being Moderated
    Hi Turloch,
    If I want to copy meta data of the tables from MS SQL to Oracle, is that possible for me to copy all the meta data of tables into Oracle DB after these tables have been copied to Oracle by using Copy to Oracle?

    Kevin
  • 4. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Kevin,

    1/'Copy to Oracle' is simple (based on generic Java metadata) more complicated metadata would have to be moved manually (or by repository method 2/) what metadata do you need? ('Copy to Oracle' does for example auto increment columns and not null, it does not do for example user defined types, indexes, triggers and foreign keys).

    2/Right click on database 'migrate to oracle' takes you into a wizard - requires a repository for storing meta data. This does for example indexes, foreign keys, TSQL (triggers, view, procedures, functions) which method 1/ above does not do automatically.

    Turloch
    -SQLDeveloper Team
  • 5. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    user480768 Newbie
    Currently Being Moderated
    Hi Turloch,
    When I use migrate to oracle, I got a problem, the migrate tool create a new schema for me in my case (AZTECA_KSMMS), it migrates all the stuffs under that schema (AZTECA_KSMMS). However my application need the all the Oracle data under schema AZTECA instead of AZTECA_KSMMS. Is there any way to specify specific schema (AZTECA) for target oracle database?
    Also during the migration process, when I choose repository, there is a check box for truncate to reset repository to empty state, Do I need to check that truncate Check Box so the repository will be cleared from last migration?
    There are also online database and offline database options during the migration process, what are the difference between these two choices? After I migrated to Oracle, all my views has a red cross icon next to it. Does that mean the view migration is failed or not? Please give me your comments. Thanks for your help.

    Kevin
  • 6. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Kevin,
    user13531850 wrote:
    Hi Turloch,
    When I use migrate to oracle, I got a problem, the migrate tool create a new schema for me in my case (AZTECA_KSMMS), it migrates all the stuffs under that schema (AZTECA_KSMMS). However my application need the all the Oracle data under schema AZTECA instead of AZTECA_KSMMS. Is there any way to specify specific schema (AZTECA) for target oracle database?
    Schema remapping is available:
    First Capture (separately) then during right click convert on the captured model there is a Specify the conversion options with a Object Naming tab where the schema (and other) name changes are editable.

    I have not used this recently.
    Also during the migration process, when I choose repository, there is a check box for truncate to reset repository to empty state, Do I need to check that truncate Check Box so the repository will be cleared from last migration?
    The repository can hold multple migration attempts. Check truncate to get rid of previous attempts information. This cleans up the repository - not the destination database.
    There are also online database and offline database options during the migration process, what are the difference between these two choices? After I migrated to Oracle, all my views has a red cross icon next to it. Does that mean the view migration is failed or not? Please give me your comments. Thanks for your help.
    offline: for big (amount of data) databases with simple data types,
    uses bcp + files + scripts + sqlldr.
    online: for small (amount of data) databases (easier),
    uses (Java) jdbc.

    The view is likely to be broken - recompiling it may help.
    The Oracle schema is created using a .sql file - see under generated in the directory you gave originally in the wizard. There is a .out file that contains the result of running this script including any errors. During conversion there are also likely to be warnings displayed on the UI.

    There may be a single issue that is causing multiple issues - if viewa depends on functionb, and functionb is broken, viewa will also fail.

    >
    Kevin
    -Turloch
    SQLDeveloper Team
  • 7. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    user480768 Newbie
    Currently Being Moderated
    Hi Turloch,
    I did twice migrations with the same schema in the Oracle db, it seems the number of row in the oracle db tables has been doubled. If I do another migration with the same schema, can I use truncate option to get rid of all the extra rows from the tables in Oracle DB? Thanks again for your comments.

    Kevin
  • 8. Re: How do I migrate views from MS SQL 2008 to Oracle 11g through SQL Developer
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Kevin,

    1/Just noticed the data move screen has an option to "truncate data" that is probably what you want, this will delete (or truncate) the rows of existing data in the relevant table in the Oracle database before moving data into it.

    2/Re running the generated script without deleting the schemas will bring up errors - on create user at the very least.
    I am usually migrating into new schemas so I just log out and:
    Delete everything before rerunning the generated script (You can get the schema names from the top of the generated script).
    drop user <the_schema1> cascade; --your schema offten dbo_<database>
    drop user <the_schema2> cascade;
    ...
    drop user emulation cascade; -- schema for migration utils
    drop public synonym utils; --might not be necessary to delete this it points to Emulation.utils

    -Turloch
    SQLDeveloper Team

Legend

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