hi, i'm trying to create a materialized view in oracle 9i to speed up some of our reports. It creates ok and the 'refresh state' goes to 'FRESH' (seen from enterprise manager console). But, in 5 mins, when the mv is set to automatically refresh, both the 'refresh state' and 'compile state' goes to 'NEEDS_COMPILE'. I have created mv_logs on all the schema/tables that the mv uses. Below is the sql i used to create the mv. TIA.

CREATE MATERIALIZED VIEW "SCHEMA1"."MV_TEST"
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('18-Jul-2006 11:00:00 AM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 5/1440
AS
select 'TEXT' type, to_char(actiondate,'YYYY') TRANS_YEAR, substr(get_desc(trans_type),30) DESC, decode(trans_type,'A', get_qty(table_b.param1,table_c.param2), get_diff_qty(table_b.param1,table_c.param2,table_a.param3)) QTY
from table_a, table_b, table_c
where table_a.trans_type in ('A','B')
and table_a.trans_type = table_b.trans_id
and table_a.actiondate = table_c.transdate (+)
and table_a.trans_type = table_c.type_id (+)
and table_a.fld1 = table_c.fldc (+)
and table_c.fldd (+) = 'value01'