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

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