Re: Materialized View does no refresh

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 Wed Jan 30 2002 - 09:19:26 CST