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

Franck Pachot
Jun 26 · 3 min read

AskTOM

sql_trace

LogMiner

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');
commit;
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
,options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+DBMS_LOGMNR.CONTINUOUS_MINE
+DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
select
operation,seg_name,commit_scn,count(*),min(scn),max(scn),pxid,xid
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

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

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