Materialized View By Example - Changing the Base Table.

General
Consider the following scenario:
There is a scheduled FAST refresh from a base table.
Now there are new column in the Base Table.
How to handle the refresh procedure so it will handle the change?

Example
For Example, there is a table GATES in schema A with a Materialized View GATES_MV in schema B, on another Instance(s).
Schema A.GATES structure changes.

Steps to handle this change:
On Schema A
1. Drop existing MLOG$_GATES table
   All MLOG$ tables are listed in USER_MVIEW_LOGS

   This is done by: DROP MATERIALIZED VIEW LOG ON GATES.


2. Re-Create MLOG$_GATES table.
   This is done by: CREATE MATERIALIZED VIEW LOG ON GATES.

   Check that MLOG$_GATES is now created.


SQL> DESC MLOG$_GATES
Name            Type        Nullable Default Comments 
--------------- ----------- -------- ------- -------- 
GATE_ID         NUMBER(10)  Y                         
SNAPTIME$$      DATE        Y                         
DMLTYPE$$       VARCHAR2(1) Y                         
OLD_NEW$$       VARCHAR2(1) Y                         

CHANGE_VECTOR$$ RAW(255)    Y 


SQL> DESC GATES;
Name                  Type         Nullable Default Comments 
--------------------- ------------ -------- ------- -------- 
GATE_ID               NUMBER(10)                             
NETWORK_ID            NUMBER(10)                             
PREFERRED_IND         NUMBER(1)              

PREFERRED_IND is the new column.

3. Run Refresh on the Remotes DB, i.e. in Schema B.

Step A
Run the COMPLETE Refresh - to validate there are no issues with the refresh.


EXEC DBMS_SNAPSHOT.REFRESH('GATES_MV','C');

Step B
Assuming there is a scheduled FAST refresh, run the FAST Refresh - To set the baseline for next scheduled refresh.

EXEC DBMS_SNAPSHOT.REFRESH('GATES_MV','F');

SQL> DESC GATES_MV;
Name                  Type         Nullable Default Comments 
--------------------- ------------ -------- ------- -------- 
GATE_ID               NUMBER(10)                             
NETWORK_ID            NUMBER(10)                             
PREFERRED_IND         NUMBER(1)             


Appendix
Nice Blog with Examples:  Materialized Views