Hello Tom,
we have a materialized view, created with
...
CREATE MATERIALIZED VIEW "mySchema"."myMView" ("Col1", "Col2", "Col3")
...
REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (sysdate +10/1440)
...
which automatically creates a DBMS_JOB that executes
dbms_refresh.refresh('"mySchema"."myMView"');
Does DBMS_REFRESH.REFRESH an atomic refresh of the MView?
According to
https://docs.oracle.com/database/121/REPMA/rarrefreshpac.htm#REPMA602 :
"REFRESH Procedure
This procedure manually refreshes a refresh group."
But from this doc I can't see if the refresh is atomic (DELETE+INSERT in single transaction) or non-atomic (TRUNCATE+INSERT).
Many thanks in advance.
The docs say this:
"Refresh Groups
To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle Database can refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time."
Of course, that could still mean they do a truncate, and the refresh them all to a consistent point in time. So lets do a trace:
SQL> create materialized view t
2 refresh complete on demand
3 as select * from dba_objects;
Materialized view created.
SQL> exec dbms_refresh.make('RG','MCDONAC.T',sysdate+10,'sysdate+30')
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_enable;
PL/SQL procedure successfully completed.
SQL> exec dbms_refresh.refresh('RG');
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable;
PL/SQL procedure successfully completed.
The resulting trace file contains:
delete from "MCDONAC"."T"
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MCDONAC"."T" select * from dba_objects
and we dont see any evidence of commits until after both steps are done:
XCTEND rlbk=0, rd_only=0, tim=166305987242
Hope this helps