1

I have an application which is getting data from Oracle(11.2.0.4) Materialized view, there is expected behaviour about materialized view will go INVALID when any DML action happens on the associated tables or assocaited objects are invalid.

My materialized view opotions are as listed below and confirming that associated objects are valid all the time.

CREATE materialized VIEW mv_emp_test1 
NOLOGGING COMPRESS build IMMEDIATE 
REFRESH FORCE 
START WITH (SYSDATE) NEXT (SYSDATE+5/1024)
as
......

Materialized view go into INVALID state when it freshes after every 5 minutes and become valid if any of the following method applied:

alter materialized view mv_emp_test1 recompile;
or
exec dbms_mview.refresh('mv_emp_test1');

Also, checked related documents and bugs, Doc ID 264036.1 in metalink has included the exact scenario but didn't find proper workaround so that auto refresh in every 5 minutes.

  • 1
    I just noticed, that you reference the 11g-r2 tag but in your question use the version 10.2.0.4. I guess that is a typo? – hot2use Jan 9 '18 at 14:51
  • Thanks hot2use. Yes, I was intended to mean 11.2.0.4, correction updated in the question. – Suresh Gautam Jan 10 '18 at 3:36
3

That is the expected behaviour.

If the data in the base table changes and the view isn't up to date anymore its status is not valid anymore. If you recompile or refresh it will become up to date and is valid again. So you don't have to bother about the invalid state of your view.

This question was already posed on stackoverfow.com and answered by Andrew Brennan:

See MOS Doc ID 264036.1:

Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

  • Thanks for the reply. Sorry to say, but it bothers me in the context of materialized view stay in the old snapshot though associated tables are keep getting additional data, and each refresh(AUTO) never bring up-to-date data in the materialized view. It results old state data display in the application as it's referring the materialized view, however, associated tables have latest data. I was looking workaround to reflect the same refresh/recompile behavior during Auto refresh, but no luck. – Suresh Gautam Jan 9 '18 at 13:12
  • @SureshGautam you can use the "ON COMMIT REFRESH" clause to get a view that will be refreshed by the same transaction as the table. But this may slowdown the speed of manupulating table data. Or maybe it is sufficient to rund the sync job more frequently, e.g. every minute. Check the links of the other posts, e.g. the "Scheduling Continuous Pushes" link of the document linked by "Synchronous Replication". MAybe hou have to make a materlialized nie log to make fast, differential syncs. – miracle173 Jan 9 '18 at 17:34
  • Thanks @miracle173 for the update comment. I have seen the options of ON COMMIT REFRESH too, but it's not suitable when you have any of the table of Materialized view is referring from remote. – Suresh Gautam Jan 10 '18 at 3:43
3

The whole idea behind a materialized view is to make the data "available" in the view and not to have to retrieve the data from the underlying tables for example from a remote location. If the data or the table definitions are modified, then the MV becomes INVALID.

Materialized Views

(in earlier versions known as snapshots)

The definition of a materialized view can be found in the original documentation:

Synchronous Replication

If you want to retrieve up-to-date data, then you might want to consider using a normal view or a synchronous replication.

Further reading:

2

If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

Probably running out of job queue processes?

SQL> alter system set job_queue_processes=0;

System altered.

SQL> select * from test;

    ID
----------
     1
     2
     3
     4


SQL> Create Materialized view test_mv 
refresh complete 
start with (sysdate) 
next (sysdate+1/1440) 
with rowid 
as 
select * from test;   

Materialized view created.

SQL> select compile_state from dba_mviews where mview_name='TEST_MV';

COMPILE_STATE
-------------------
VALID

SQL> select * from test_mv;

    ID
----------
     1
     2
     3
     4


SQL> insert into test values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> select compile_state from dba_mviews where mview_name='TEST_MV';

COMPILE_STATE
-------------------
NEEDS_COMPILE

Wait for one minute...

SQL> select * from test_mv;

    ID
----------
     1
     2
     3
     4

The change has not reflected yet.

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

PL/SQL procedure successfully completed.

SQL> select compile_state from dba_mviews where mview_name='TEST_MV';

COMPILE_STATE
-------------------
VALID

Now, it's in a valid state.

As others have stated, it's an expected behavior of an MV to be in NEEDS_COMPILE state. However, it should automatically refresh your MV in every 5 minutes of interval.

SQL> alter system set job_queue_processes=1000;

System altered.

SQL> select compile_state from dba_mviews where mview_name='TEST_MV';

COMPILE_STATE
-------------------
VALID

SQL> insert into test values(6);

1 row created.

SQL> commit;

Commit complete.

SQL> select compile_state from dba_mviews where mview_name='TEST_MV';

COMPILE_STATE
-------------------
NEEDS_COMPILE

Let's wait a minute...

SQL> /

COMPILE_STATE
-------------------
VALID

It has become valid.

  • I already have job_queue_processes=1000 in the server in which I am getting the issue reported in my question and issue remains same. – Suresh Gautam Jan 10 '18 at 4:42
  • @SureshGautam: It would be easier for us to test the case if you could provide a sample DDL, including joins of the MV. We do have the same kind of MVs which we fresh once in a day using a DBMS_SCHEDULER job and it works fine. – JSapkota Jan 10 '18 at 4:51
  • I think it's worth going with dbms_scheduler to refresh the MV in my case. Thanks. – Suresh Gautam Jan 10 '18 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.