Materialized-View changes its compile state to error after refresh
Posted on 2004-08-18
I've made a Materialized View (Oracle 8i) like this:
CREATE MATERIALIZED VIEW MY_MVIEW
START WITH to_date(to_char(sysdate, 'dd-mon-yy')||' 23:30','DD-MON-YY HH24:MI')
NEXT trunc(SYSDATE + 1) + 47/48
WITH PRIMARY KEY
DISABLE QUERY REWRITE
Btw.: It contains a UNION and subselects.
After its creation the entry in the table DBA_MVIEWS looks good --> Compile State: VALID, Rewrite Enabled: N.
Then I simulate the refresh with SQL> execute dbms_refresh.refresh("MY_MVIEW");
and now the DBA_MVIEW tells me: Compile State: ERROR, Rewrite Enabled: Y!!!
ALTER MATERIALIZED VIEW MY_VIEW COMPILE doesn't help, in fact it creates the same result.
Actually I'm still able to use the MView for SELECTs. But I have to be sure that 'Rewrite Enabled' (I guess this means Query Rewrite?) is Disabled!
I'm quite new in using MViews, so maybe someone can help me with that?