0 Replies Latest reply: Mar 16, 2012 9:55 AM by UW (Germany) RSS

    Inconsistent information about registered mviews

    UW (Germany)
      From the past I know situations, where DBA_REGISTERED_MVIEWS shows MVIEW_IDs that are not represented in DBA_BASE_TABLE_MVIEWS. Such a situation can be corrected with DBMS_MVIEW.REGISTER_MVIEW or DBMS_MVIEW.UNREGISTER_MVIEW depending whether the materialized view still exists or not.

      But now I have a case where DBA_BASE_TABLE_MVIEWS shows a record that does not exist in DBA_REGISTERED_MVIEWS:
      select * from sys.dba_registered_mviews where name like '%GENE%';
      
      OWNER NAME          MVIEW_SITE      CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION                    QUERY_TXT                                                                 
      ----- ------------- --------------- ----------- --------- -------------- -------- -------------------------- --------------------------------------------------------------------------
      STAGE MV_GENERATION DWH.MYSITE.DE   YES         NO        PRIMARY KEY         465 ORACLE 8 MATERIALIZED VIEW select * from GENERATION@ORA11PRD.MYSITE.DE@DWH_READ_PRODUCTION_LOCATION  
      STAGE MV_GENERATION DWH2.MYSITE.DE  YES         NO        PRIMARY KEY         884 ORACLE 8 MATERIALIZED VIEW SELECT * FROM "DWH_READ"."GENERATION"@"ORA11PRD@DWH_READ_PRODUCTION_LOCATI
      
      select * from sys.dba_base_table_mviews where master like '%GENE%';
      
      OWNER    MASTER      MVIEW_LAST_REFRESH_TIME MVIEW_ID
      -------- ----------- ----------------------- --------
      EXAMPLE  GENERATION  07.10.2011 11:53:35         1535
      EXAMPLE  GENERATION  16.03.2012 06:31:20          465
      EXAMPLE  GENERATION  16.03.2012 13:52:56          884
      
      select * from sys.dba_registered_mviews where MVIEW_ID = 1535;
      
      no rows selected
      
      {code}
      
      How can such an inconsistent situation occur? How can I fix it? Where can I find more information about the mview with ID 1535?
      
      The tables shown above are on an 11.1.0.7. database. The replication goes to a 10.2.0.3.0 (DWH.MYSITE.DE) and 11.2.0.3.0 (DWH2.MYSITE.DE).
      I only changed some names of tables, owners and sites for anonymization. DWH_READ.GENERATION is a synonym on EXAMPLE.GENERATION.