Create materialized view on prebuilt table

With a slow network bandwidth between the transaction and data warehouse servers, using create mview on prebuilt table can speed up the mview creation

• Export data of the needed table.
• Transfer the export file to the destination
• Import data to create table.
• Create materialized view on prebuilt table

create materialized view DEST.TABLE
on prebuilt table
using index tablespace INDX refresh force as
select * from SRC.TABLE@DBLINK;

• Perform fast refresh.

exec dbms_mview.refresh('TABLE','F');

References:

• http://arup.blogspot.com/2009/01/ultra-fast-mv-alteration-using-prebuilt.html
• http://hemantoracledba.blogspot.com/2009/03/materialized-view-on-prebuilt-table.html