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.