8 Replies Latest reply: Jan 25, 2013 12:58 PM by Turloch O'Tierney RSS

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

    user480768
      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
          Turloch O'Tierney
          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
            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
              Turloch O'Tierney
              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
                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
                  Turloch O'Tierney
                  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
                    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
                      Turloch O'Tierney
                      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