Re: refreshing materialized views in Oracle 8i

Hi Jusung,

Thanks for your response. I tried the below which did not work.

exec DBMS_MVIEW.REFRESH('MARFISDW.VESS_DAILY_FISH_YEAR_SUM_TEST', 'C', '', TRUE, FALSE, 0,0,0,FALSE) ERROR at line 1:

ORA-01426: numeric overflow 
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617 
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674 
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654 
ORA-06512: at line 1 

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<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');
>
>
> - Jusung Yang
>
>
> 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 - 20:22:01 CEST