Re: Materialized View does no refresh

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 Thu Jan 31 2002 - 12:26:01 CST