SQL> 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> -- Only the primary, the default, is included in the MV log SQL> create materialized view log on test3_a; Materialized view log created. SQL> desc mlog$_test3_a; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 VARCHAR2(2) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) SQL> SQL> -- M_ROW$$, which contains the ROWID info., is added SQL> alter materialized view log on test3_a add rowid; Materialized view log altered. SQL> desc mlog$_test3_a; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 VARCHAR2(2) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) M_ROW$$ VARCHAR2(255) SQL> SQL> -- c2 now added SQL> alter materialized view log on test3_a add(c2); Materialized view log altered. SQL> desc mlog$_test3_a; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 VARCHAR2(2) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) M_ROW$$ VARCHAR2(255) C2 NUMBER SQL> SQL> -- The "including new values" does not change MV LOG structure, just the data captured. SQL> alter materialized view log on test3_a add(c3) including new values; Materialized view log altered. SQL> desc mlog$_test3_a; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 VARCHAR2(2) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) M_ROW$$ VARCHAR2(255) C2 NUMBER C3 VARCHAR2(3) SQL> SQL> insert into test3_a values('a',1,'#1'); 1 row created. SQL> column m_row$$ format a30 SQL> column old_new$$ format a10 SQL> column dmltype$$ format a10 SQL> set head on SQL> -- 1 row added to the MV LOG, DMLTYPE is 'I' for insert 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 N I AAANzCAAJAAAGA4AAA SQL> commit; Commit complete. SQL> SQL> -- Update the priamry key of the only row captured in the MV LOG SQL> update test3_a set c1='b'; 1 row updated. SQL> -- 2 rows added to the MV LOG, 1 old, 1 new. Old for delete(D), new for insert(I) 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 N I AAANzCAAJAAAGA4AAA a 1 #1 O D AAANzCAAJAAAGA4AAA b 1 #1 N I AAANzCAAJAAAGA4AAA SQL> rollback; Rollback complete. SQL> -- rollback will also remove the rows from the MV LOG 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 N I AAANzCAAJAAAGA4AAA SQL> SQL> -- Update the non-primary key column SQL> update test3_a set c2=7; 1 row updated. SQL> -- Note the differences than updating the primary key. SQL> -- Only 1 row is added, old(O) record for update(U) 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 N I AAANzCAAJAAAGA4AAA a 1 #1 O U AAANzCAAJAAAGA4AAA SQL> rollback; Rollback complete. 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 N I AAANzCAAJAAAGA4AAA SQL> SQL> -- Update another non-primary key column, same result. SQL> update test3_a set c3='#10'; 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 N I AAANzCAAJAAAGA4AAA a 1 #1 O U AAANzCAAJAAAGA4AAA SQL> rollback; Rollback complete. 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 N I AAANzCAAJAAAGA4AAA SQL> SQL> -- Delete the row already captrued in the MV will ad 1 row, old for delete, to the MV LOG SQL> delete from test3_a; 1 row deleted. 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 N I AAANzCAAJAAAGA4AAA a 1 #1 O D AAANzCAAJAAAGA4AAA SQL> rollback; Rollback complete. SQL> SQL> drop table test3_a; Table dropped. SQL> SQL> spool off