Materialized View does no refresh

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 - 07:37:48 CST