This discussion is archived
11 Replies Latest reply: Jul 18, 2011 8:35 AM by mkirtley-Oracle RSS

How to only migrate data from SQL Server 2008 to Oracle 11?

873590 Newbie
Currently Being Moderated
According to our requirement, We need to only migrate data from a SQL Server database to an existed
Oracle database user.
1) I tried to do it with SQL Developer 3.0.04 Migration Wizard, But find an issue.
My SQL Server database name is SCDS41P2, and my Oracle database user name is CDS41P2;
When I used SQL Developer to do offline move data by Migration Wizard, I found all oracle user
name in movedata files which gotten by run Migration Wizard
is dbo_SCDS41P2. If the Oracle user name is not the same as my existed Oracle user name,
the data can't be moved to my existed Oracle user when I run oracle_ctl.bat in command line window.
So I had to modify the Oracle user name in all movedata files, but it's difficult to modify them because there are many tables in
databases. So could you please tell me how to get the movedata files which the oracle user name in them is my
expected Oracle user name?

2) I also tried to use the 'copy to Oracle' function to copy the SQL Server database tables data
to the existed Oracle database user. When clicked 'copy to Oracle', I selected 'Include Data' and 'Replace' option
But I found some tables can't be copied, the error info is as below:
Table SPSSCMOR_CONTROLTABLE Failed. Message: ORA-00955: name is already used by an existing object

Could you please tell me how to deal with this kind of error?

Thanks!

Edited by: 870587 on Jul 6, 2011 2:57 AM
  • 1. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    I'll check out your issues and get back to you.

    Regards,
    Mike
  • 2. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    The only way I could reproduce the copy problem error ORA-955 was to have a table in the Oracle database with the same name and choose the 'Indicate Error' option.
    If I choose 'Append' or 'Replace' then the rows are inserted wiithout any error.
    To check further could you do the following -

    - what does describe show for the SPSSCMOR_CONTROLTABLE in SQL*Server ?
    - what does describe show for the SPSSCMOR_CONTROLTABLE in Oracle ?

    Is there any difference in the column descriptions ?

    What is the 'Build' version of your SQL*Developer ?

    Regards,
    Mike
  • 3. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    For your first issue about the datamove files having the name of the database included in the user name - there is currently no way round this.
    The Migration workbench is designed for one-off migrations from non-Oracle databases to Oracle and not primarily as a 'data move' tool.
    It could be dangerous and cause problems for existing tables with the same name so it is currently designed to use the migration generated owner name.
    However, I can see it would be useful to be able to change the owner if you wanted to do this, so we will follow up and see if anything can be done in a future version.

    Regards,
    Mike
  • 4. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    Barry McGillin Journeyer
    Currently Being Moderated
    You've two options here.
    1. Copy to oracle and choose append as Mike suggests. This will work fine.
    2. Second option is to covert your sqlserver db with SQLDeveloper and generate the datamove scripts which include sqlloader scripts and abcp scripts. In the migration wizard, you can change the schema name before you convert and your scripts will have the right name. Alternatively, generate the sqlloader scripts and change the control files manually.
    B
  • 5. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    After following up with development the following should also work -

    1. Use the migration option to capture and convert the SQL*Server database
    2. Go back into SQL*Developer and delete the converted model.
    3. Click on the captured model and choose 'Convert'.
    4. Click next and you should get a screen with 2 options - 'Data Type Mapping' and 'Object Naming'
    5. Choose 'Object Naming' and scroll down the list until you see the name of the user that will be converted. For example, in my test I had the names scott_test and dbo_test - where "scott" and "dba" are the owners and "test" was the name of the database.
    6. Change the name from oner_database to just owner - scott_test to scott
    7. Complete the convert process and the owners should be created without the database name.
    8. You shoudl now be able to generate the SQL*Loader scripts with the only the owner name in the CTL files.

    Please try this and let us know if it works for you.

    Regards,
    Mike
  • 6. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    I should have added that you will the schema names in the 'Object Naming' list under 'Source Type' 'MD_SCHEMAS'.

    Regards,
    Mike
  • 7. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    873590 Newbie
    Currently Being Moderated
    Hi,

    Thanks for you replying. But the 'copy to oracle' function still can't be work well. I will give some info about the table. I also search 'SPSSCMOR_CONTROLTABLE' in the target schema, and only find one object. So why say 'name is already used by an existing object'? Could you please give me some advice? Thanks!

    What is the 'Build' version of your SQL*Developer ?
    [Answer]:
    3.0.04

    - what does describe show for the SPSSCMOR_CONTROLTABLE in SQL*Server ?
    [Answer]:
    USE [SCDS41P2]
    GO

    /****** Object: Table [dbo].[SPSSCMOR_CONTROLTABLE] Script Date: 07/18/2011 01:25:05 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[SPSSCMOR_CONTROLTABLE](
         [tablename] [nvarchar](128) NOT NULL,
    PRIMARY KEY CLUSTERED
    (
         [tablename] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    - what does describe show for the SPSSCMOR_CONTROLTABLE in Oracle ?
    [Answer]:
    --------------------------------------------------------
    -- File created - Monday-July-18-2011
    --------------------------------------------------------
    --------------------------------------------------------
    -- DDL for Table SPSSCMOR_CONTROLTABLE
    --------------------------------------------------------

    CREATE TABLE "CDS41P2"."SPSSCMOR_CONTROLTABLE"
    (     "TABLENAME" NVARCHAR2(128)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" ;
    --------------------------------------------------------
    -- DDL for Index SYS_C009547
    --------------------------------------------------------

    CREATE UNIQUE INDEX "CDS41P2"."SYS_C009547" ON "CDS41P2"."SPSSCMOR_CONTROLTABLE" ("TABLENAME")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" ;
    --------------------------------------------------------
    -- Constraints for Table SPSSCMOR_CONTROLTABLE
    --------------------------------------------------------

    ALTER TABLE "CDS41P2"."SPSSCMOR_CONTROLTABLE" MODIFY ("TABLENAME" NOT NULL ENABLE);

    ALTER TABLE "CDS41P2"."SPSSCMOR_CONTROLTABLE" ADD PRIMARY KEY ("TABLENAME")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" ENABLE;

    Edited by: 870587 on Jul 18, 2011 1:42 AM
  • 8. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    873590 Newbie
    Currently Being Moderated
    Hello Barry,

    Thanks for your options.
    For 1, we only use 'replace', because if we select append, then the data in the tables may be invalid
    for us. So that we can't install our product in the target Oracle database.

    For 2. Yes. You are correct. I can change the control files manually. But if I have 1000 tables, then
    I need modify 1000 files manually. It will cost much efforts. So I think it's difficult to do it for customers.

    We hope that some work around can be gotten so that we can only copy data from SQL Server to Oracle easily.
  • 9. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    For the issue with option 2 - did you see my update about changing the object owners using 'Object Mapping' - Posted: Jul 11, 2011 2:49 PM ?

    This should generate the SQL*Loader files with the correct names so that you do not need to change them manually.
    Please try that and let us know what happens.

    Regards,
    Mike
  • 10. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    873590 Newbie
    Currently Being Moderated
    Hello Mkirtley,

    Thanks for your 'changing Object Naming' work around.

    I have tried, and the name can be changed by this work around.
  • 11. Re: How to only migrate data from SQL Server 2008 to Oracle 11?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Okay, that's good. Thanks for letting us know.

    Regards,
    Mike

Legend

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