Re: refreshing materialized views in Oracle 8i
Date: 13 Jun 2003 00:53:05 -0700
Message-ID: <130ba93a.0306122353.2b29f775_at_posting.google.com>
Did you try refreshing it manually? Whatever the problem is, it will
become apparent when you run
dbms_refresh.refresh('"MARFISDW"."VESS_DAILY_FISH_YEAR_SUM_TEST"','c');
glen_mackay_at_yahoo.com (Glenn MacKay) wrote in message news:<831d3f98.0306121555.2ac418de_at_posting.google.com>...
> 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
> FROM TEST1, TEST2
> WHERE TEST1.cola = TEST2.fola;
>
> The one below will not refresh. It has about 120000 records,
> aggregates by month and goes against four tables. The largest table
> has about 3 million records, the smaller ones about 900 records. The
> larger table gets added to on a nightly basis, so we have to refresh
> the MV every night. Here is the script.
>
> 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 - 09:53:05 CEST