locked
"Counting Objects to be loaded" issue - SSMA for Oracle v7.6.0 RRS feed

  • Question

  • Hello,



    Hope somebody will be able to help out...

    Once I connect to my Oracle instance, I am getting the above message in the bottom bar with the progress bar going on indefinitely.


    I have set the logging to "Debugging" and the migration assistant seems to be hanging on the below query.

    I am using Oracle database v12.2.0.1 64-bit , full client (installed in Administrator mode) 12.2.0.1 64-bit.

    I have tried to connect as OLEDB Provider, Oracle Client Provider - same thing.

    I have granted all necessary privileges to the hr user. I have also tried connecting as system - makes absolutely no difference.

    Any help would be highly appreciated.



    Thanks.



    ---------------

    [Debug: Collector] [7916/8] [2017-11-06 13:42:10]: SELECT COUNT(*) FROM ( SELECT 1                                                   
            FROM sys.all_objects Objs                                                         
            WHERE Objs.object_type IN ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'SEQUENCE', 'VIEW', 'MATERIALIZED VIEW', 'SYNONYM') 
              AND Objs.subobject_name IS NULL  AND Objs.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') UNION ALL SELECT 1
            FROM sys.all_tables Tbls                                                        
            WHERE Tbls.nested <> 'YES'  AND TRIM(Tbls.instances) <> '0'  AND Tbls.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') UNION ALL SELECT 1
            FROM sys.all_object_tables Tbls                                                        
            WHERE Tbls.nested <> 'YES'  AND Tbls.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') UNION ALL SELECT 1 
            FROM sys.ALL_TYPES
            WHERE typecode IS NOT NULL  AND owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') UNION ALL SELECT 1 
            FROM ALL_POLICIES   WHERE OBJECT_OWNER NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')  )


    Monday, November 6, 2017 1:58 PM

Answers

  • Hi soti84,

    Have you tried running this same query manually from SQL Developer? How many objects do you have in the database?

    If there is a particular schema that you want to migrate, it may be reasonable to create new user account and give it access to that schema only (+system ones), so that it does not have to spend time discovering everything else.

    Regards,
    Alex.

    • Marked as answer by soti84 Tuesday, November 28, 2017 9:47 AM
    Monday, November 20, 2017 5:22 PM

All replies

  • Hi soti84,

    We are currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 5:03 AM
  • Hello Lin,

    Has any progress been made on this issue?

    Thanks.

    Thursday, November 16, 2017 9:15 AM
  • Hi soti84,

    Have you tried running this same query manually from SQL Developer? How many objects do you have in the database?

    If there is a particular schema that you want to migrate, it may be reasonable to create new user account and give it access to that schema only (+system ones), so that it does not have to spend time discovering everything else.

    Regards,
    Alex.

    • Marked as answer by soti84 Tuesday, November 28, 2017 9:47 AM
    Monday, November 20, 2017 5:22 PM
  • Hello Alexander,

    Thank you for the suggestion - this should have occurred to me to try.

    I ran the query in SQL Developer and it took an hour and a half but completed. This led me to be more patient when waiting for SSMA to finish which, oddly enough, took about double the time.

    I am not too bothered about the timings but the end result.

    Regards,

    Soti

    Tuesday, November 28, 2017 9:47 AM