Invalid Materialized View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Invalid Materialized View

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    Invalid Materialized View

    I've got a bit of a strange one here (8.1.7.4 on UNIX) - A MV that is INVALID when I query USER_OBJECTS but when I compile it via SQL PLUS it says it compiles but still remains INVALID:

    Code:
    SQL> select count(*) from MV_MOVEMENT_PROCESSES
      2  /
    
      COUNT(*)
    ----------
       2050895
    
    SQL> select status, object_type from user_objects where object_name='MV_MOVEMENT_PROCESSES';
    
    STATUS  OBJECT_TYPE
    ------- ------------------
    VALID   TABLE
    INVALID MATERIALIZED VIEW
    
    SQL> alter MATERIALIZED VIEW MV_MOVEMENT_PROCESSES compile;
    
    Materialized view altered.
    
    SQL> select status, object_type from user_objects where object_name='MV_MOVEMENT_PROCESSES';
    
    STATUS  OBJECT_TYPE
    ------- ------------------
    VALID   TABLE
    INVALID MATERIALIZED VIEW
    The status in USER_MVIEWS show COMPILATION_ERROR - how can I find why this is INVALID??
    I've also kicked of a refresh that seems to be working and hasn't complained the MV is INVALID??

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Actually using 9.2.0.4, not 8.1.7.4!!

    Even more confused now the refresh has worked:

    Code:
    SQL> execute dbms_mview.refresh('MINE1.MV_MOVEMENT_PROCESSES');
    
    PL/SQL procedure successfully completed.
    
    SQL> select status, object_type from user_objects where object_name='MV_MOVEMENT_PROCESSES';
    
    STATUS  OBJECT_TYPE
    ------- ------------------
    VALID   TABLE
    INVALID MATERIALIZED VIEW

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Bug.2695199/2639679 - MATERIALIZED VIEWS BECOME INVALID AFTER ALTER OR REFRESH ??
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width