Pre-built materialized views

In a recent Oraclesponge posting David Aldridge writes about a way to get around the “ORA-12034: materialized view log younger than last refresh” error. Of course his method relies on you knowing what it is about the MV that needs to be fixed to get it back into to sync with its source tables (or what needs to be fixed in the source tables to sync it to the MV). In other words, if you don’t make the changes correctly then the MV will not be a true result of the defining query and the database will not realise this!
Key to David’s technique was the use of MVs that are defined on pre-built tables. One thing that surprises me about the Oracle Data Warehouse guide, and indeed many other books discussing MVs is the way in which the use of pre-built tables is often relegated to a single comment on the lines of “you can also define a MV on a pre-built table” But creating an MV and its storage in a single operation can have drawbacks: the initial build is basically the whole table in a single select statement, this can have enormous resource issues for any large materialized view; the creation of a partitioned MV including its storage is a nasty looking piece of DDL; and if you need to drop the MV for some reason (such as the fix proposed by David) then BANG, all of the data in it goes too as the underlying table is dropped. So for me in my data warehouse world that is three good reasons to use pre-built tables.
In the past I needed to ‘edit’ a MV definition, in my case I had a nested set of MVs and I needed to change the defining query for an aggregation MV to improve its likelihood for selection in query rewrite, I was not changing the content of the MV but just the way it was created. But it is not possible to edit MVs the only thing you can do is drop followed by create. As the original view took around 12 hours to build, I did not want go through the pain of losing all of my aggregated data just to make a minor change to the view definition. So, how do you convert an existing MV to a MV on a pre-built table – there is no ALTER MATERIALIZED VIEW command to do it. The simple answer is partition exchange. If your MV is partitioned then you need to create an identically structured but empty partitioned table and then exchange each MV partition to an intermediate table before exchanging the intermediate table with the new table’s partition; when all the partitions have been exchanged the original MV dropped and a new MV defined on the pre-built table. If the source MV is not partitioned you still can do exchange, but this time the intermediate table is partitioned (it only needs one partition)

Edited to correct an error about tablespaces