Oracle Materialized View Refresh Group atomicity— How to prove transactional consistency with LogMiner

Franck Pachot
Jun 26 · 3 min read




set numwidth 16 linesize 200 pagesize 1000
column scn1 new_value scn1
column scn2 new_value scn2
column sid format 999999 new_value sid
column seg_type format 99
column seg_name format a30
column seg_owner format a12
column operation format a12
set linesize 1000
alter database add supplemental log data;
select current_timestamp,current_scn scn1,sys_context('userenv','sid') sid from v$database;
exec dbms_refresh.refresh('LSA.CCDB_VIEWS');
select current_timestamp,current_scn scn2,sys_context('userenv','sid') sid from v$database;alter database drop supplemental log data;
exec dbms_logmnr.start_logmnr(startscn=>&scn1,endscn=>&scn2
from v$logmnr_contents
where session#=sys_context('userenv','sid') -- my session
and (seg_owner,seg_name) in ( -- my refresh group mviews
select owner,name
from dba_rchild
where refgroup in (
select refgroup
from dba_rgroup
where owner='LSA' and name='CCDB_VIEWS'
group by operation,seg_name,commit_scn,xid,pxid
order by commit_scn,max(scn)

Franck Pachot

Written by DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services:

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade