none
completed-no-objects on initial full import RRS feed

  • Question

  • I've configured an Oracle MA and it connects to the db (Oracle 11g) and reads the table schema without any problem. However, when I do a Full Import it shows a connection status as "success" but a overall status of "completed-no-objects". I have put data in the table via Oracle SQL Developer and know it's there. I had the DBA watch the Oracle server when the full import is running and this is what he reported was happening:

    /* Formatted on 11/1/2012 5:54:01 PM (QP5 v5.139.911.3011) */
      SELECT *
        FROM (SELECT NULL TABLE_CATALOG,
                     DECODE (owner, 'PUBLIC', NULL, owner) TABLE_SCHEMA,
                     object_name TABLE_NAME,
                     DECODE (
                        owner,
                        'SYS', DECODE (object_type,
                                       'TABLE', 'SYSTEM TABLE',
                                       'VIEW', 'SYSTEM VIEW',
                                       object_type),
                        'SYSTEM', DECODE (object_type,
                                          'TABLE', 'SYSTEM TABLE',
                                          'VIEW', 'SYSTEM VIEW',
                                          object_type),
                        'DMSYS', DECODE (object_type,
                                         'TABLE', 'SYSTEM TABLE',
                                         'VIEW', 'SYSTEM VIEW',
                                         object_type),
                        'ORDSYS', DECODE (object_type,
                                          'TABLE', 'SYSTEM TABLE',
                                          'VIEW', 'SYSTEM VIEW',
                                          object_type),
                        'EXFSYS', DECODE (object_type,
                                          'TABLE', 'SYSTEM TABLE',
                                          'VIEW', 'SYSTEM VIEW',
                                          object_type),
                        'WMSYS', DECODE (object_type,
                                         'TABLE', 'SYSTEM TABLE',
                                         'VIEW', 'SYSTEM VIEW',
                                         object_type),
                        'MDSYS', DECODE (object_type,
                                         'TABLE', 'SYSTEM TABLE',
                                         'VIEW', 'SYSTEM VIEW',
                                         object_type),
                        'CTXSYS', DECODE (object_type,
                                          'TABLE', 'SYSTEM TABLE',
                                          'VIEW', 'SYSTEM VIEW',
                                          object_type),
                        'OLAPSYS', DECODE (object_type,
                                           'TABLE', 'SYSTEM TABLE',
                                           'VIEW', 'SYSTEM VIEW',
                                           object_type),
                        'WKSYS', DECODE (object_type,
                                         'TABLE', 'SYSTEM TABLE',
                                         'VIEW', 'SYSTEM VIEW',
                                         object_type),
                        object_type)
                        TABLE_TYPE,
                     NULL TABLE_GUID,
                     NULL DESCRIPTION,
                     NULL TABLE_PROPID,
                     created DATE_CREATED,
                     last_ddl_time DATE_MODIFIED
                FROM all_objects
               WHERE object_type IN ('TABLE', 'VIEW')
              UNION
              SELECT NULL table_catalog,
                     DECODE (o2.owner, 'PUBLIC', NULL, o2.owner) table_schema,
                     o2.object_name table_name,
                     o2.object_type table_type,
                     NULL table_guid,
                     NULL description,
                     NULL table_propid,
                     o2.created DATE_CREATED,
                     o2.last_ddl_time DATE_MODIFIED
                FROM all_objects o2, all_objects o3, all_synonyms s
               WHERE     o2.object_type = 'SYNONYM'
                     AND (o3.object_type = 'TABLE' OR o3.object_type = 'VIEW')
                     AND o2.owner = s.owner
                     AND o2.object_name = s.synonym_name
                     AND s.table_owner = o3.owner
                     AND s.table_name = o3.object_name) DBSCHEMA_TABLES
    ORDER BY 4 DESC, 3 ASC, 2 ASC

    I don't notice anything in there about the specific table name. Anyone have any ideas how to fix this? Thanks.

    Steve



    • Edited by StevenJ. _ Monday, November 5, 2012 9:04 PM
    Friday, November 2, 2012 3:23 PM

Answers

  • Well, it came down to needing a "commit" in Oracle. My Oracle developer must not have had a commit command in the stored procedure that populated the table. And in my testing in Oracle SQL Developer, having never using Oracle before, I didn't know I had to commit the changes. So I'd see the data in the table while in SQL Developer, but FIM wouldn't since the transactions weren't committed. Such fun when you fight for a week with such basic stuff. :(
    • Marked as answer by StevenJ. _ Wednesday, November 7, 2012 4:18 PM
    Wednesday, November 7, 2012 4:18 PM