Folks,
Working on Oracle 11g, the problem that I have detailed below appears to show my MV is not refreshing, I am not sure why, maybe I missed a step, any hints much appreciated:-
SQL> create table t
2 (
3 col1 varchar2(10)
4 );
Table created.
SQL>
SQL> alter table t add constraint pk_col1 primary key (COL1);
Table altered.
SQL> insert into t select object_name from all_objects where rownum < 6;
5 rows created.
SQL> commit;
Commit complete.
SQL> select * from t;
COL1
----------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
SQL> create materialized view log on t with primary key;
Materialized view log created.
SQL> create materialized view mv
2 refresh fast on demand
3 start with sysdate next trunc(sysdate,'mi') + 1/24/12
4 as
5 select col1 from t;
Materialized view created.
SQL> select * from mv;
COL1
----------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
SQL> alter table t add col2 varchar2(20);
Table altered.
SQL> update t set col2 = 'hello';
5 rows updated.
SQL> commit
2 ;
Commit complete.
SQL> select col1,col2 from t;
COL1 COL2
---------- --------------------
ICOL$ hello
I_USER1 hello
CON$ hello
UNDO$ hello
C_COBJ# hello
SQL> select * from mv;
SQL> alter materialized view mv add col2 varchar2(20);
Materialized view altered.
SQL> select * from mv;
COL1 COL2
---------- --------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
SQL> EXEC DBMS_MVIEW.REFRESH('MV');
PL/SQL procedure successfully completed.
SQL> select * from mv;
COL1 COL2
---------- --------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#