Re: Materialized View does no refresh

George,

Check table dba_jobs to see if column BROKEN is set to N. Thats means that the MV will not be build. See http://metalink.oracle.com for more info about broken jobs.

kind regards,

Ivan

george wrote:

> yup it is set to 4 - still nothing happens - the session just hangs
> (?)
>
> I have 1.5 m rows on the target in test and maybe 5 m in production
> so I would expect it to be slower but not imovable
>
> tried everything does anyone know where I can check the status of the
> view of the status of the job in progress
>
> Thanks
>
> George
>
> Ivan Saez <i.a.saez.scheihing_at_tue.nl> wrote in message news:<3C580EFE.B62E625D_at_tue.nl>...
> > George,
> >
> > Don't forget to set job_queue_processes in your init.ora file:
> >
> > job_queue_processes = 3
> >
> > kind regards,
> >
> > Ivan
> > george wrote:
> >
> > > I did this in our test system
> > >
> > > created the log
> > > drop materialized view log on inv_sum
> > >
> > > CREATE MATERIALIZED VIEW log on INV_SUM
> > > with primary key, rowid (
> > > EXTENDED_NET_PRC ,
> > > C_EXTENDED_NET_PRC ,
> > > ENG_EXTENDED_NET_PRC ,
> > > ENG_C_EXTENDED_NET_PRC ,
> > > INVOICED_QTY ,
> > > C_INVOICED_QTY ,
> > > ENG_INVOICED_QTY ,
> > > ENG_C_INVOICED_QTY ,
> > > EXTENDED_DISCOUNT_AMT ,
> > > ENG_EXTENDED_DISCOUNT_AMT ,
> > > C_EXTENDED_DISCOUNT_AMT ,
> > > ENG_C_EXTENDED_DISCOUNT_AMT ,
> > > ORDERED_QTY ,
> > > C_ORDERED_QTY ,
> > > ENG_ORDERED_QTY )
> > > including new values ;
> > >
> > > I had to use with primary key, rowid - I do not understand why I
> > > would have thought I only needed primary key ??? but anyway
> > >
> > > recreated the MV - and rebulit it
> > >
> > > DROP MATERIALIZED VIEW mv_INV_sum_MONTH
> > >
> > > CREATE MATERIALIZED VIEW MV_INV_sum_MONTH
> > > --PARALLEL
> > > PCTFREE 0 TABLESPACE DWMRGDVDATA
> > > STORAGE (initial 50m next 50m pctincrease 0)
> > > BUILD IMMEDIATE
> > > REFRESH FAST ON DEMAND --with rowid
> > > ENABLE QUERY REWRITE
> > > AS
> > > SELECT to_number(to_char(invoice_date,'mm')) as invoice_month,
> > > to_number(to_char(invoice_date,'yyyy')) as invoice_year,
> > > inv_sum.source_id,
> > > inv_sum.customer_num, inv_sum.producing_division_code,
> > > inv_sum.sps_subgroup_code,
> > > SUM (inv_sum.extended_net_prc) as sum_extended_net_prc,
> > > SUM (inv_sum.c_extended_net_prc) as
> > > sum_c_extended_net_prc,
> > > SUM (inv_sum.eng_extended_net_prc) as
> > > sum_eng_extended_net_prc,
> > > SUM (inv_sum.eng_c_extended_net_prc) as
> > > sum_eng_c_extended_net_prc,
> > > SUM (inv_sum.invoiced_qty) as sum_invoiced_qty ,
> > > SUM (inv_sum.c_invoiced_qty) as sum_c_invoiced_qty ,
> > > SUM (inv_sum.eng_invoiced_qty) as sum_eng_invoiced_qt,
> > > SUM (inv_sum.eng_c_invoiced_qty) as sum_eng_c_invoiced_qty,
> > > SUM (inv_sum.extended_discount_amt) as sum_extended_discount_amt,
> > > SUM (inv_sum.eng_extended_discount_amt) as
> > > sum_eng_extended_discount_amt,
> > > SUM (inv_sum.c_extended_discount_amt) as
> > > sum_c_extended_discount_amt,
> > > SUM (inv_sum.eng_c_extended_discount_amt) as
> > > sum_eng_c_extended_disc_amt ,
> > > SUM (inv_sum.ordered_qty) as sum_ordered_qty,
> > > SUM (inv_sum.c_ordered_qty) as sum_c_ordered_qty,
> > > SUM (inv_sum.eng_ordered_qty) as sum_eng_ordered_qty,
> > > count (inv_sum.extended_net_prc) as count_extended_net_prc
> > > ,
> > > count (inv_sum.c_extended_net_prc) as
> > > count_c_extended_net_prc,
> > > count (inv_sum.eng_extended_net_prc) as
> > > count_eng_extended_net_prc,
> > > count (inv_sum.eng_c_extended_net_prc) as
> > > count_eng_c_extended_net_prc,
> > > count (inv_sum.invoiced_qty) as count_invoiced_qty,
> > > count (inv_sum.c_invoiced_qty) as count_c_invoiced_qty,
> > > count (inv_sum.eng_invoiced_qty) as count_eng_invoiced_qty,
> > > count (inv_sum.eng_c_invoiced_qty) as
> > > count_eng_c_invoiced_qty,
> > > count (inv_sum.extended_discount_amt) as
> > > count_extended_discount_amt,
> > > count (inv_sum.eng_extended_discount_amt) as
> > > count_eng_extended_disc_amt ,
> > > count (inv_sum.c_extended_discount_amt) as
> > > count_c_extended_discount_amt,
> > > count (inv_sum.eng_c_extended_discount_amt) as
> > > count_eng_c_extended_disc_amt,
> > > count (inv_sum.ordered_qty) as count_ordered_qty,
> > > count (inv_sum.c_ordered_qty) as count_c_ordered_qty,
> > > count (inv_sum.eng_ordered_qty) as count_eng_ordered_qty,
> > > count(*)
> > > FROM inv_sum
> > > GROUP BY to_number(to_char(invoice_date,'mm')),
> > > to_number(to_char(invoice_date,'yyyy')),
> > > inv_sum.source_id,
> > > inv_sum.customer_num,
> > > inv_sum.producing_division_code,
> > > inv_sum.sps_subgroup_code
> > >
> > >
> > >
> > > copied todays data down from our production system
> > >
> > > insert into inv_sum (select * from inv_sum_at_smdmprod_link where
> > > invoice_date = '28-jan-02' )
> > >
> > > commit ;
> > >
> > > comitted - viewed the log to see if it was full of data (it was)
> > >
> > > ran the refresh - took around 30 seconds
> > >
> > > BEGIN
> > > DBMS_MVIEW.REFRESH('mv_inv_sum_month', 'F', '', TRUE, FALSE, 0,0,0,
> > > FALSE);
> > > end ;
> > >
> > > when I do the same in production NOTHING APPEARS TO HAPPEN no error
> > > messages - the refresh seems to start - I can see the SQL when using
> > > TOAD
> > > to monitor the sessions but it never seems to get anywhere
> > >
> > > any help would be greatly appreciated - fyi we are 8.1.7 in test and
> > > 8.1.6
> > > in production (why I will never know .. )
Received on Fri Feb 01 2002 - 00:23:13 CST