Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

Is it possible that the column existed but was dropped/renamed and a new column created with the same name ?

Is everything being done from the same schema/user ? It is possible to do grants at a column level, but it isn't something I've seen used much.

A trace may provide some interesting information (ie the text of the SQL statement which it couldn't parse)

share|improve this answer
    
I don't believe the column is being dropped/renamed, due to another MV across the same columns working correctly. Please see my edit for more information. Everything is happening in the same schema, yes. I don't know if I'm able to get a trace. I'll look into that now. It is a 3rd party java application that does the ETL from staging, to these warehouse tables I'm creating the MVs on. –  Josh Smeaton Jul 22 '11 at 1:12
add comment

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.

share|improve this answer
    
It's not a fast refresh though. I rebuilt all of the MV logs yesterday along with all of the existing MV's. Any other thoughts? I'll check on the MV log when I get in to work just incase. –  Josh Smeaton Jul 21 '11 at 23:29
    
It is a fast refresh: REFRESH ON COMMIT (from the docs): "Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table...". –  DCookie Jul 22 '11 at 22:31
    
I was under the impression REFRESH ON COMMIT does a complete refresh. REFRESH FAST ON COMMIT will do it incrementally. I had to remove the FAST clause since it is a intra-day table that is truncated daily, and hence isn't fast refreshable after truncation. Please correct me if I'm misunderstanding though. –  Josh Smeaton Jul 23 '11 at 5:35
    
Unless I'm reading the docs wrong, it's pretty clear that it's a fast refresh that's done. Would you really want a complete refresh of your MV on every commit on the master? That would really slow down any commits. I just don't see how that would work. –  DCookie Jul 23 '11 at 18:33
    
the complete refresh is necessary on these intra-day tables since they are truncated once a day. A fast refresh fails on a truncated table. The complete refresh happens once every 15 minutes (when the table is filled), and is ok, since it is only one days worth of data. At the end of each day, the data is moved into a historical table, which IS fast refreshed. I'm certain that this is not a FAST refresh. I appreciate your help though. –  Josh Smeaton Jul 24 '11 at 22:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.