Top 60 Oracle Blogs

Recent comments

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

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

I had recently to prove to myself, and then to the Oracle Support, that a Materialized View Group was not refreshed atomically as it should, according to the documentation:

Add materialized views to a refresh group to ensure transactional consistency between the related materialized views in the refresh group. When a refresh group is refreshed, all materialized views that are added to a particular refresh group are refreshed at the same time.

Database Administrator's Guide


The first idea was suggested by the user who encountered the issue. He has read a similar question on, AskTOM, which is a very good idea:

Ask TOM does DBMS_REFRESH.REFRESH a atomic refresh of mviews?

There, Connor quickly shows that there are no intermediate commits by enabling SQL_TRACE and looking at the XCTEND lines from the trace dump.


So I did the same in my case and here is an extract from the interesting lines, just grepping the inserts and the XCTEND:

Clearly, the first 4 tables were done in the same transaction. But we can see some commits between the 4 others. This seemed to confirm what the user has observed: a query on tables shows data from a different point in time. And then I opened a SR to fill a bug.

However, the support engineer disapproved this proof because the XCTEND can come from recursive transactions. And he is totally right. With SQL_TRACE you can prove that it is atomic, but you cannot prove that it is not.


When it comes to transactions, LogMiner is the right tool. It is incredibly powerful (all persistent changes on your database go to the redo stream, and LogMiner can read the most interesting out of it. And it is incredibly easy to use — at least until the latest release where Oracle removes many replication features which may overlap with GoldenGate — a product sold separately.

Here I’m refreshing the materialized view group LSA.CCDB_VIEWS, which contains 8 MVIEWs. I am tracking the SCN before (scn1) and after (scn2).

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;

Note that as I don’t have supplemental logging enabled here, I enable it just for this test. It is not a bad idea to enable it always, as long as the redo size is acceptable.

Then I start Log Miner for this SCN range. I use CONTINUOUS_MINE as I am on 18c here (it has been deprecated, de-supported and even removed in 19c