(First sorry for accidentally replying only to Jared on his questions. Damn gmail) Update. After some more testing it turns out that what was tested was slightly invalid. 1 of the 3 MV's used was in fact a Fast refresh MV on which the other 2 were partially built. In that scenario my previous test results stand. If that 1 Fast refresh MV is taken out and replaced with another MV similar to the other 2 (i.e. Complete refresh and built on the other MV that was taken out) then "atomic" = TRUE seems to work as advertised. I will do more testing but this appears to solve the problem. Incidentally, the read consistent (for the end user) refresh of MV's seems to only be available with the dbms_mview package, which can specify the atomic parameter. dbms_refresh doesn't have that option : DBMS_REFRESH.MAKE ( name IN VARCHAR2 { list IN VARCHAR2, | tab IN DBMS_UTILITY.UNCL_ARRAY,} next_date IN DATE, interval IN VARCHAR2, implicit_destroy IN BOOLEAN := false, lax IN BOOLEAN := false, job IN BINARY INTEGER := 0, rollback_seg IN VARCHAR2 := NULL, push_deferred_rpc IN BOOLEAN := true, refresh_after_errors IN BOOLEAN := false purge_option IN BINARY_INTEGER := NULL, parallelism IN BINARY_INTEGER := NULL, heap_size IN BINARY_INTEGER := NULL); If I run into any other issues with this I will update the list. Otherwise, consider it solved. Special thanks to Jared Still for taking the time to test and much more. Thanks, Finn On Thu, Nov 6, 2008 at 9:01 AM, Finn Jorgensen <finn.oracledba@xxxxxxxxx>wrote: > Jared, > > >Are these complete refreshes? > > Yes. The MV's use the max function call and group by so they have to be. > > >How is it you know that the refresh is not taking place in a single > transaction? > > I haven't had the time to do the testing myself so I'm relaying on a > developer providing me with accurate information. He says he has 3 MV's that > take about a minute each to do a complete refresh. He has 2 sessions open to > the DB. He updates a row in an underlying table, commits it and then selects > the row from the 3 MV's that should be updated. In the other window he then > kicks off the refresh in the other session. After a minute the updted row > shows up in the first session. After another minute the 2nd MV shows the row > and so on. Not very scientific but certainly valid IMHO. > > I tried specifying atomic_refresh=>true and I still saw the above behavior > when using dbms_mview.refresh(list=>LIST). When specifying > atomic_refresh=>false it initially appears a truncate is used on all MV's in > the list and then data is inserted in all the MV's in one transaction. I > will have to dig deeper into this one. > > I will try your test case in my db and see what I get. > > Keep the ideas and suggestions coming, please. > > Thanks, > Finn > > >