Re: refreshing materialized views in Oracle 8i

Hi All,

Jut to close this thread I found the problem. THe clause had to contain REFRESH COMPLETE, not just REFRESH. I thought COMPLETE was the default.

Glenn

glen_mackay_at_yahoo.com (Glenn MacKay) wrote in message news:<831d3f98.0306131022.50a70c73_at_posting.google.com>...
> 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
>
> Then I tried the following which did work. Notice how the last
> parameter is set to true instead of false.
> exec DBMS_MVIEW.REFRESH('MARFISDW.VESS_DAILY_FISH_YEAR_SUM_TEST', 'C',
> '', TRUE, FALSE, 0,0,0,TRUE)
>
> Apparently there are some bugs related to ora-1426 and ATOMIC_REFRESH.
> I'll have to do some more research as I don't even know what
> ATOMIC_REFRESH means. One is bug 1340052, another is bug 2014116.
> I'll try a few more things and let you know if I fix it. I'm going to
> try it with REFRESH COMPLETE in my clause. I noticed I don't have
> that.
>
> Thanks Again,
>
> Glenn
>
> 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 Tue Jun 17 2003 - 14:10:12 CEST