SQL>
SQL> /* What happen when you truncate MV log and lose the captured change data?
DOC> Notice the differences in the old_new$$ column.
DOC> Instead of 'O' nad 'N', they are now 'U' and 'N'.
DOC>
DOC> Never a good idea to mess with the MV LOG manually.
DOC> Instead, perform complete refresh or recreate MV LOG when problems arise.
DOC>
DOC> It has been reported that truncate an empty log will disable fast refresh.
DOC> However, it can not be reproduced here.
DOC>*/
* What happens during empty MV log truncate?
SQL> drop table test3_a;
drop table test3_a
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table test3_a(c1 varchar2(2), c2 number, c3 varchar2(3));
Table created.
SQL> alter table test3_a add constraint test3_a_pk primary key (c1);
Table altered.
SQL>
SQL> insert into test3_a values('a',1,'#1');
1 row created.
SQL>
SQL> create materialized view log on test3_a
2 with primary key, rowid(c2,c3)
3 including new values;
Materialized view log created.
SQL> desc mlog$_test3_a;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(2)
C2 NUMBER
C3 VARCHAR2(3)
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
SQL>
SQL> create materialized view mv_test3_a
2 refresh fast on demand
3 as select count(*), c1, sum(c2), count(c2)
4 from test3_a group by c1;
Materialized view created.
SQL>
SQL> column m_row$$ format a30
SQL> column old_new$$ format a10
SQL> column dmltype$$ format a10
SQL> set head on
SQL>
SQL> update test3_a set c2=7;
1 row updated.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
C1 C2 C3 OLD_NEW$$ DMLTYPE$$ M_ROW$$
-- ---------- --- ---------- ---------- ------------------------------
a 1 #1 O U AAANaiAAJAAAARIAAA
a 7 #1 N U AAANaiAAJAAAARIAAA
SQL> commit;
Commit complete.
SQL> update test3_a set c2=8;
1 row updated.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
C1 C2 C3 OLD_NEW$$ DMLTYPE$$ M_ROW$$
-- ---------- --- ---------- ---------- ------------------------------
a 1 #1 O U AAANaiAAJAAAARIAAA
a 7 #1 N U AAANaiAAJAAAARIAAA
a 7 #1 O U AAANaiAAJAAAARIAAA
a 8 #1 N U AAANaiAAJAAAARIAAA
SQL> commit;
Commit complete.
SQL>
SQL> truncate table mlog$_test3_a;
Table truncated.
SQL> update test3_a set c2=8;
1 row updated.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
C1 C2 C3 OLD_NEW$$ DMLTYPE$$ M_ROW$$
-- ---------- --- ---------- ---------- ------------------------------
a 8 #1 U U AAANaiAAJAAAARIAAA
a 8 #1 N U AAANaiAAJAAAARIAAA
SQL>
SQL> execute dbms_mview.refresh('mv_test3_a','c');
PL/SQL procedure successfully completed.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
no rows selected
SQL> select last_refresh_type from user_mviews where mview_name='MV_TEST3_A';
LAST_REF
--------
COMPLETE
SQL> select * from mv_test3_a;
COUNT(*) C1 SUM(C2) COUNT(C2)
---------- -- ---------- ----------
1 a 8 1
SQL>
SQL> -- Truncate an empty log
SQL> alter session set sql_trace=true;
Session altered.
SQL> truncate table mlog$_test3_a;
Table truncated.
SQL> alter session set sql_trace=false;
Session altered.
SQL> update test3_a set c2=2;
1 row updated.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
C1 C2 C3 OLD_NEW$$ DMLTYPE$$ M_ROW$$
-- ---------- --- ---------- ---------- ------------------------------
a 8 #1 O U AAANaiAAJAAAARIAAA
a 2 #1 N U AAANaiAAJAAAARIAAA
SQL>
SQL> execute dbms_mview.refresh('mv_test3_a','f');
PL/SQL procedure successfully completed.
SQL> select c1,c2,c3,old_new$$,DMLTYPE$$,m_row$$ from mlog$_test3_a;
no rows selected
SQL> select last_refresh_type from user_mviews where mview_name='MV_TEST3_A';
LAST_REF
--------
FAST
SQL> select * from mv_test3_a;
COUNT(*) C1 SUM(C2) COUNT(C2)
---------- -- ---------- ----------
1 a 2 1
SQL>
SQL> drop materialized view mv_test3_a;
Materialized view dropped.
SQL> drop table test3_a;
Table dropped.
SQL> spool off