Error updating a master table of a materialized view

materialized-vieworacle

I've created several materialized views, and all but one are working correctly. The specific error I'm getting (via a Java job) is this:

error loading from table 'STG_INTERACTION_RESOURCE_FACT' to table 'R_INTERACTION_RESOURCE_FACT'
ORA-12048: error encountered while refreshing materialized view "GIM76"."MED_SEG_FACT_EXT_R"
ORA-00904: "MAS$3"."USER_DATA_20": invalid identifier

No errors occur when generating the MV. Refreshing it manually works correctly also:

execute DBMS_MVIEW.REFRESH('GIM76.MED_SEG_FACT_EXT_R','C');

The error only occurs when the java program attempts to move data from a staging table into the master tables. The MV is quite large, so I'll only post a fragment of the actual query:

CREATE MATERIALIZED VIEW GIM76.MED_SEG_FACT_EXT_R
PARALLEL
BUILD IMMEDIATE REFRESH
ON COMMIT
AS
select 
    -- other columns elided for brevity
    coalesce(irf.user_data_1,if.user_data_1) as i_user_data_1, 
    coalesce(irf.user_data_2,if.user_data_2) as i_user_data_2, 
    coalesce(irf.user_data_3,if.user_data_3) as i_user_data_3, 
    coalesce(irf.user_data_4,if.user_data_4) as i_user_data_4, 
    coalesce(irf.user_data_5,if.user_data_5) as i_user_data_5, 
    coalesce(irf.user_data_6,if.user_data_6) as i_user_data_6, 
    coalesce(irf.user_data_7,if.user_data_7) as i_user_data_7, 
    coalesce(irf.user_data_8,if.user_data_8) as i_user_data_8, 
    coalesce(irf.user_data_9,if.user_data_9) as i_user_data_9, 
    coalesce(irf.user_data_10,if.user_data_10) as i_user_data_10, 
    coalesce(irf.user_data_11,if.user_data_11) as i_user_data_11, 
    coalesce(irf.user_data_12,if.user_data_12) as i_user_data_12, 
    coalesce(irf.user_data_13,if.user_data_13) as i_user_data_13, 
    coalesce(irf.user_data_14,if.user_data_14) as i_user_data_14, 
    coalesce(irf.user_data_15,if.user_data_15) as i_user_data_15, 
    coalesce(irf.user_data_16,if.user_data_16) as i_user_data_16, 
    coalesce(irf.user_data_17,if.user_data_17) as i_user_data_17, 
    coalesce(irf.user_data_18,if.user_data_18) as i_user_data_18, 
    coalesce(irf.user_data_19,if.user_data_19) as i_user_data_19, 
    coalesce(irf.user_data_20,if.user_data_20) as i_user_data_20, 
    -- other columns elided
    msf.rowid as msf_rid,
    if.rowid as if_rid,
    cust.rowid as cust_rid,
    vrf.rowid as vrf_rid,
    irf.rowid as irf_rid
from 
    gim76.r_mediation_segment_fact msf, 
    gim76.r_interaction_fact if,
    gim76.customer cust,
    gim76.r_voice_res_fact_ext vrf,
    gim76.r_interaction_resource_fact irf
where 
    msf.interaction_id = if.interaction_id
and msf.target_ixn_resource_id = irf.interaction_resource_id (+)
and cust.customer_key (+) = irf.customer_key 
and irf.ixn_res_fact_ext_key = vrf.voice_res_fact_ext_key (+);

I can guarantee that the column user_data_20 exists in both tables (ir and irf). This should be obvious since the MV generates fine, and updates manually fine.

Am I missing something here? I don't understand the MAS$3 syntax, of what that refers to.

Edit:

I'm creating another MV that uses very similar columns. Instead of doing a coalesce on the troublesome column though, it just uses the value from the first table. This one works correctly.

CREATE MATERIALIZED VIEW GIM76.IRF_EXT_R
PARALLEL
BUILD IMMEDIATE REFRESH
ON COMMIT
AS
select  
    -- other columns
    irf.user_data_1 as user_data_1, 
    irf.user_data_2 as user_data_2, 
    irf.user_data_3 as user_data_3, 
    irf.user_data_4 as user_data_4, 
    irf.user_data_5 as user_data_5, 
    irf.user_data_6 as user_data_6, 
    irf.user_data_7 as user_data_7, 
    irf.user_data_8 as user_data_8, 
    irf.user_data_9 as user_data_9, 
    irf.user_data_10 as user_data_10, 
    irf.user_data_11 as user_data_11, 
    irf.user_data_12 as user_data_12, 
    irf.user_data_13 as user_data_13, 
    irf.user_data_14 as user_data_14, 
    irf.user_data_15 as user_data_15, 
    irf.user_data_16 as user_data_16, 
    irf.user_data_17 as user_data_17, 
    irf.user_data_18 as user_data_18, 
    irf.user_data_19 as user_data_19, 
    irf.user_data_20 as user_data_20, 
    -- other columns
    irf.rowid as irf_rid,
    if.rowid as if_rid,
    cust.rowid as cust_rid,
    vrf.rowid as vrf_rid
from  
    gim76.r_interaction_resource_fact irf,
    gim76.r_interaction_fact if,
    gim76.customer cust,
    gim76.r_voice_res_fact_ext vrf
where 
    irf.interaction_id = if.interaction_id
and irf.customer_key = cust.customer_key
and irf.ixn_res_fact_ext_key = vrf.voice_res_fact_ext_key (+);

Could the problem be that multiple MVs are created on the same tables/fields? I couldn't see how that could have anything to do with it, but I'm merely a developer getting his dba feet wet at the moment.

Second Edit:

It appears the order of the coalescing statement matters in my particular case. If I reverse the order of the fields in the coalesce statement, it appears to work correctly. It definitely works if I remove the coalesce altogether.

Is there some kind of race condition that may be the cause of this? I find it extremely strange.

Best Answer

Just a guess, but since you're doing a fast refresh, you have an MV Log, which is what I would presume MAS$3 is. Perhaps the column user_data_20 has been added recently, and is not in the MV Log? You might have to rebuild your MV log on one or both tables.