[MV] - refresh problem

Giganews Newsgroups
Subject: [MV] - refresh problem
Posted by:  jacquesh (jhul…@gmail.com)
Date: 20 Nov 2006

Hello,
I've a strange problem with an Oracle 8.1.7.4 DB.

I've a table 'BRANCARD'

I create a MV with auto-refresh.

<code>
CREATE MATERIALIZED VIEW MV_00087_BRANCARD
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)
TABLESPACE BRANCA
REFRESH FAST
WITH PRIMARY KEY
START WITH SYSDATE NEXT SYSDATE+1/1440
FOR UPDATE DISABLE QUERY REWRITE AS
select * from brancard where no_hop='00087' order by no_branc,ty_branc;

Materialized view created.
</code>

my problem :

At the first refresh...
<code>
select MVIEW_NAME, UPDATABLE, REWRITE_ENABLED, REWRITE_CAPABILITY,
REFRESH_MODE, REFRESH_METHOD, STALENESS, AFTER_FAST_REFRESH,
COMPILE_STATE from all_mviews

MVIEW_NAME                    U R REWRITE_C REFRES REFRESH_ STALENESS
AFTER_FAS COMPILE_STATE
------------------------------ - - --------- ------ -------- ---------
--------- -------------
MV_00087_BRANCARD              Y N TEXTMATCH DEMAND FAST    FRESH
FRESH    NEEDS_COMPILE
VUE_00087_ETA                  N N GENERAL  DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_PASSINT              N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_PDT                  N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_PLTTECH              N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_SER                  N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_UAD                  N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_UFP                  N N GENERAL  DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
VUE_00087_UHPDT                N N NONE      DEMAND COMPLETE UNDEFINED
UNDEFINED VALID
</code>

the refresh failed without error. only 'NEEDS_COMPILE' value in
COMPILE_STATE !!

If i try an 'alter compile' it's OK
If i try an execute DBMS_snapshot.refresh('MV_00087_BRANCARD', '?')
it's ok

....but the refresh is not automatic ;((

any idea about this strange behaviour ?

Replies