Skip to Main Content
  • Questions
  • Materialized View On Commit Refresh failure

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: May 27, 2020 - 1:27 am UTC

Last updated: May 27, 2020 - 1:00 pm UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Team,

Thanks a lot for all the support you have provided so far :)

I have created an On Commit Materialized view MVA recently on tableA. Primary Key M View log is used here.
Occasionally this refresh fails with ORA-12034 error.
Since this is a key table in our application this failure causes lot of issues for us.

While searching in Oracle Alert logs to findout why is this refresh failing, I could see below alert.

Refresh failed due to missing on-commit privilege for "Schema"."MVB"
Following on-commit snapshots not refreshed :
schema.MVB
schema.MVA

MVB is another ON Commit M View created on different set of tables and doesnt have any reference to tableA/MVA.
I am not sure if this alert message is directly related to the ORA-12034 error i am facing.

Could you please suggest on how to diagnose this further

Note : tableA and MVA are created in same schema. TableB/c/d and MVB are also present in same schema as tableA/MVA.

I could see that last_purge_date of the M View log for tableA was less than last refresh date of MVA.
However, there was no forced purge done.
Is there any way to find how this purge happened on M view log?

Please let me know if you need more details on this

Thanks,
Manoj

and Chris said...

MOS note 204127.1 has a big list of things which may lead to ORA-12034 errors.

If you've narrowed it down to something purging the MV log, be aware that there are several DDL commands that can purge the log. Notably truncate table and partition operations:

create table t (
  c1 int primary key , c2 int
) partition by range ( c1 ) interval ( 1 ) (
  partition p1 values less than ( 2 ) 
);

create materialized view log on t
  with rowid, primary key ( c2 )
  including new values;
  
insert into t 
with rws as (
  select level x from dual
  connect by level <= 10
)
  select x, x from rws;
commit;

select count(*) from mlog$_t;

COUNT(*)   
         10 

alter table t 
  truncate partition p1;

select count(*) from mlog$_t;

COUNT(*)   
          0 


So check to see if you've got any maintenance operations like these on the table. Failing this, look to see if you can add some auditing to the MV log to capture what exactly purges it.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.