Share via

ssmafororacleconsole - SYS_IOT_OVER warnings

Leandro Martins de Lima 1 Reputation point
2020-11-05T14:50:15.497+00:00

Hello, folks.

About a year ago we started a project for a home made ETL to migrate some Oracle into SQL Server databases. In this project we're using SSMA exclusively to perform the data copy since the database models have some specificities not treatable by SSMA.

Everything is working perfectly, our ETL performs the model tanslation, uses SSMA for the main data copy job and then it index the SQL database after.

But then we recently added the index-organized tables in our Oracle environment. Like I said, the translation from Oracle to SQL is not made by SSMA, we pre-create the tables beforehand into the SQL Server before migrating the data with SSMA, but since this new type was added I remarked some different warnings in the SSMA log:

    [2020-11-05 09:22:30]  WARNING SSMA Schema Collector component 'Oracle Server Object Collector' reported an error
           > Description: No columns were returned from the database for table 'SYS_IOT_OVER_2015027'.

           > Metabase Object: table POR2020.SYS_IOT_OVER_2015027

           >
    [2020-11-05 09:22:33]  WARNING SSMA Schema Collector component 'Oracle Server Object Collector' reported an error
           > Description: No columns were returned from the database for table 'SYS_IOT_OVER_2015024'.

           > Metabase Object: table POR2020.SYS_IOT_OVER_2015024

           >
    [2020-11-05 09:22:35]  WARNING SSMA Schema Collector component 'Oracle Server Object Collector' reported an error
           > Description: No columns were returned from the database for table 'SYS_IOT_OVER_2015033'.

           > Metabase Object: table POR2020.SYS_IOT_OVER_2015033

I know that these are internal tables on which it the Oracle IOT technology is built and should not (cannot) be directly interacted with. The question is, why is SSMA queryinig them? Those are not errors and the data copy is correctly done, just that these warnings pollute the output log. The SSMA log is not exactly clean by default, but having as many as 500 IOTs (which each add four warning lines to the log) will pollute it even more.

As with some other special Oracle types (like LOB columns which generate internal SYS objects) SSMA should ignore these SYS_IOT tables, shouldn't it?

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
517 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Xiao Yu 81 Reputation points Microsoft Employee
    2020-11-20T00:36:12.773+00:00

    Thanks for the detail. I tried to repro this issue, but it did not load these index organized tables at all. Could you try to run below queries manually to see if it contains the SYS_IOT_OVER_* tables?

    SELECT Usrs.user_id
    , Objs.object_name
    , Objs.object_type
    , Objs.created
    , Objs.last_ddl_time
    , Objs.temporary
    , Objs.status AS status
    , decode(cluster_name,null,0,1) as is_clustered
    , NULL as is_in_memory
    , NULL as table_type_name
    , NULL as table_type_owner
    , CASE WHEN Mv.CONTAINER_NAME IS NOT NULL THEN 1 ELSE 0 END AS is_mat_view_table
    FROM sys.dba_objects Objs
    , sys.dba_mviews Mv
    , sys.dba_users Usrs
    , sys.dba_tables Tbls
    WHERE Objs.object_type = 'TABLE'
    AND Objs.subobject_name IS NULL
    AND Objs.owner = Usrs.username
    AND Objs.owner = Tbls.owner
    AND Objs.object_name = Tbls.table_name
    AND Tbls.nested <> 'YES'
    AND TRIM(Tbls.instances) <> '0'
    AND Tbls.dropped <> 'YES'
    AND Mv.OWNER(+) = Objs.OWNER AND Mv.CONTAINER_NAME(+) = Objs.OBJECT_NAME
    AND Objs.owner IN (<schema name>)

    If it returns SYS_IOT_OVER_* tables, could you share a sample of index organized table ddl?

    0 comments