locked
SSMA 5.2 for Oracle O2SS0486 Error RRS feed

  • Question

  • First time using SSMA and I was hoping to get some help with an error I am seeing.

    I am working on a project to migrate a very old Oracle 8.0.5 database over to SQL Server (currently targeting 2005 with a long-term solution to then upgrade to SQL Server 2012). I was able to load the Oracle client and get SSMA connected to my source Oracle database and destination SQL Server instance. I selected the schema that I wanted to convert over and ran an assessment report to determine what potential migration problems there are. On the report I am seeing errors on several tables stating it is failing to convert a primary or unique key.

    O2SS0486: SQL Server Migration Assistant for Oracle Error message: Primary or unique constraint not converted.

    If I drill-down and select one of the tables that could not be converted all I get are the table statistics showing that it could not be converted. I do not see any detail of why it could not be converted. No error message, no warning, nothing. I did a search online and the only hit I got for this error message was from a person with a problem who had other error messages as well and it does not seem that particular issue was resolved.

    I would appreciate any assistance or ideas.


    Joie Andrew "Since 1982"

    Tuesday, June 18, 2013 9:25 AM

Answers

  • I figured it out. Actually the problem was not that I needed to disable constraints on the Oracle database, but that primary and unique constraints were disabled already and SQL was not able to handle that. SQL Server cannot disable primary key or unique constraints.

    Interesting thing though, and something I wanted to ensure that I share is that I was not able to find this information out from the assessment report or the Oracle Metadata Explorer at all. It was only when I went to a specific table that had the error and then ran Convert Schema that a more detailed error message was displayed:

    O2SSO486: <schema>.<object>.<constraint> constraint is disabled in Oracle and cannot be converted becaues SQL Server does not support disabling of primary or unique constraint.

    Since this was already set in production (most likely many years ago as an on-site Oracle dba/dev has not been available for that long) I can safely ignore this message for now. After my migration I can investigate adding the constraints back.


    Joie Andrew "Since 1982"

    • Marked as answer by Joie Andrew Tuesday, June 18, 2013 11:20 AM
    Tuesday, June 18, 2013 11:20 AM

All replies

  • O2SS0486: SQL Server Migration Assistant for Oracle Error message: Primary or unique constraint not converted.

    


    Joie Andrew "Since 1982"

    Hi,

    You need to disable contrainst on table col present in Oracle database..disable contraints like PK or Unique key and then try,u may need to take downtime in oracle DB for that

    Also SSMA says that if some of ur procs/codes it is not able to convert you should not select it during conversion so u will not get any error later u have to manually convert it


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, June 18, 2013 10:01 AM
  • I figured it out. Actually the problem was not that I needed to disable constraints on the Oracle database, but that primary and unique constraints were disabled already and SQL was not able to handle that. SQL Server cannot disable primary key or unique constraints.

    Interesting thing though, and something I wanted to ensure that I share is that I was not able to find this information out from the assessment report or the Oracle Metadata Explorer at all. It was only when I went to a specific table that had the error and then ran Convert Schema that a more detailed error message was displayed:

    O2SSO486: <schema>.<object>.<constraint> constraint is disabled in Oracle and cannot be converted becaues SQL Server does not support disabling of primary or unique constraint.

    Since this was already set in production (most likely many years ago as an on-site Oracle dba/dev has not been available for that long) I can safely ignore this message for now. After my migration I can investigate adding the constraints back.


    Joie Andrew "Since 1982"

    • Marked as answer by Joie Andrew Tuesday, June 18, 2013 11:20 AM
    Tuesday, June 18, 2013 11:20 AM