INVALID Materialized View NEEDS_COMPILE

This example was tested on Oracle 11.2. It shows how an on demand materialized view becomes invalid following DML on the underlying table. First I created a table:

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL>

Then I inserted the value 1 into it: 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL>

I created an on demand materialized view on the table: 

SQL> create materialized view mv1

  2  refresh on demand

  3  as select * from tab1

  4  /

 

Materialized view created.

 

SQL>

I checked that it was VALID:

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL>

I updated the table, making the materialized view out of date: 

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         2

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL>

Although you can still query the materialized view when this happens, it shows as INVALID:

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL>

... and in need of compilation:

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            NEEDS_COMPILE

 

SQL>

This isn't a problem but, if you are not happy with it, you can refresh the materialized view:

SQL> exec dbms_mview.refresh('MV1');

 

PL/SQL procedure successfully completed.

 

SQL>

This brings it in line with the underlying table:

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>

It is then shown as VALID again:

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL>

... and not in need of compilation:

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            VALID

 

SQL>

I updated the table, making the materialized view out of date again: 

SQL> update tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         3

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

SQL>

It was also INVALID and in need of compilation as before:

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            NEEDS_COMPILE

 

SQL>

Another way to change this, if it bothers you, is to compile it:

SQL> alter materialized view mv1 compile

  2  /

 

Materialized view altered.

 

SQL>

This way, the materialized view is still out of date: 

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>

... but it is no longer INVALID: 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL>

... and it does not need compiling either:

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            VALID

 

SQL>