refreshing materialized views in Oracle 8i

Hi All,

I am trying to have Materialized Views (MV) automatically refresh in a data warehouse. I tried a test MV called VM_TEST which is shown below. This one will refresh.

CREATE MATERIALIZED VIEW VM_TEST
  PCTFREE 30
  TABLESPACE CE_USERS
  USING INDEX TABLESPACE CE_INDEXES
  STORAGE (INITIAL 300K NEXT 300K)
  REFRESH START WITH SYSDATE
  NEXT ROUND(SYSDATE + 1) + 4/24 ENABLE QUERY REWRITE AS   SELECT

   TEST1.COLA, 
   TEST2.FOLA, 
   TEST1.COLB, 
   TEST2.FOLB 

CREATE MATERIALIZED VIEW VESS_DAILY_FISH_YEAR_SUM_TEST PCTFREE 30
  TABLESPACE CE_USERS
  USING INDEX TABLESPACE CE_INDEXES
  STORAGE (INITIAL 300K NEXT 300K)
  REFRESH START WITH SYSDATE
  NEXT ROUND(SYSDATE + 1) + 4/24 ENABLE QUERY REWRITE AS SELECT fact.dwd_vessel_id_landing,

 		 fact.dwd_licence_id,
		 fact.dwd_participant_id,
  		 ddate.ye_id YEAR,
		 dcomm.sector_id,
		 dcomm.sector_name_eng,
		 dcomm.sector_name_fre,
		 spc.spc_species_code, 
		 spc.cat_id,
		 spc.cat_desc_eng,
		 spc.cat_desc_fre,
		 spc.spc_desc_eng,
		 spc.spc_desc_fre,
		 spc.spc_lic_desc_eng,
		 spc.spc_lic_desc_fre,     
	  	 SUM(fact.catch_value) SUM_CATCH_VALUE, 
	  	 SUM(fact.rnd_weight_kgs) SUM_RND_WEIGHT_KGS    
  FROM 	 DW_INTEGRATED_CATCH  fact,DWD_DATES ddate,
  	   	 DWD_SPECIES spc, DWD_COMMUNITIES dcomm
  WHERE  fact.dwd_specie_id_round = spc.dwd_specie_id
  AND 	 fact.dwd_community_id = dcomm.dwd_community_id
  AND 	 fact.dwd_date_id_landed = ddate.dwd_date_id 
  GROUP BY fact.dwd_vessel_id_landing,
  		   fact.dwd_licence_id,
		   fact.dwd_participant_id,
		   dcomm.sector_id,
		   dcomm.sector_name_eng,
		   dcomm.sector_name_fre,
  		   spc.spc_species_code,
		   spc.cat_id,
		   spc.cat_desc_eng,
		   spc.cat_desc_fre,
		   spc.spc_desc_eng,
		   spc.spc_desc_fre,
		   spc.spc_lic_desc_eng,
		   spc.spc_lic_desc_fre,  
		   ddate.ye_id;

I've tried TRUNC(SYSDATE + 1) + 4/24 as well and nothing works. It shows in dba_jobs as below.

LAST_DATE	       NEXT_DATE	    BROKEN INTERVAL
06/12/03 4:04:24 AM	06/13/03 4:00:00 AM	N  TRUNC(SYSDATE + 1) + /24 

WHAT
dbms_refresh.refresh('"MARFISDW"."VESS_DAILY_FISH_YEAR_SUM_TEST"');

Our job_queue_processes is set to 4. Version is 8.1.7.4.1.

I am confused about why one MV refreshes and the other one will not. Any help would be appreciated. Thanks.

Glenn MacKay

PS I would have used MV logs but I THINK that this MV statement is not compatible with their use. Received on Fri Jun 13 2003 - 01:55:04 CEST