Re: - refresh problem

jacquesh wrote:
> 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 ?

Difficult to tell, as you don't include the table definition, so no one can verify whether the table actually has a primary key. Secondly, if I recall correctly, for a mv or snapshot to fast refresh you need a *materialized view log*. I don't see one here. Also the order by may be a cause for the fast refresh to fail, but I would need to check this in the documentation.
Finally, there should have been created a pl/sql job. Did you verify this in dba_jobs or user_jobs.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA