SQL> SQL> /* DOC>This simple dummy MV has no problem with fast refresh on base table DOC>containing clob. DOC> DOC>*/ SQL> SQL> drop table t1_a; drop table t1_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table t1_a (id number, c1 clob); Table created. SQL> alter table t1_a add constraint t1_a_pk primary key (id); Table altered. SQL> insert into t1_a values(1,'xxx'); 1 row created. SQL> insert into t1_a values(2,'yyy'); 1 row created. SQL> SQL> create materialized view log on t1_a; Materialized view log created. SQL> create materialized view t1_a_mv 2 refresh fast on demand 3 as select * from t1_a; Materialized view created. SQL> select * from t1_a_mv; ID ---------- C1 -------------------------------------------------------------------------------- 1 xxx 2 yyy SQL> SQL> -- Simple update to the clob column SQL> update t1_a set c1='xxx + some more text here' where id=1; 1 row updated. SQL> SQL> -- Trgger fired... SQL> select * from mlog$_t1_a; ID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ---------- -------------------- ---------- ---------- ---------------- 1 010100 00:00:00 U U 04 SQL> exec dbms_mview.refresh('t1_a_mv','f'); PL/SQL procedure successfully completed. SQL> -- MV refreshed OK... SQL> select * from mlog$_t1_a; no rows selected SQL> select * from t1_a_mv; ID ---------- C1 -------------------------------------------------------------------------------- 1 xxx + some more text here 2 yyy SQL> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews 2 where mview_name='T1_A_MV'; LAST_REF LAST_REFRESH_DA -------- --------------- FAST 270703 12:44:36 SQL> SQL> -- Using BDMS_LOB package to do the update SQL> declare 2 x clob; 3 begin 4 select c1 into x from t1_a where id=2 for update; 5 dbms_lob.append(x,' + added text with dbms_lob.append'); 6 end; 7 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> -- Trigger fired SQL> select * from mlog$_t1_a; ID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ---------- -------------------- ---------- ---------- ---------------- 2 010100 00:00:00 U U 0400000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ---------- -------------------- ---------- ---------- ---------------- 0000000000000000 0000000000000000 0000000000000000 0000000000000000 000000000000 SQL> exec dbms_mview.refresh('t1_a_mv','f'); PL/SQL procedure successfully completed. SQL> -- MV refreshed OK... SQL> select * from mlog$_t1_a; no rows selected SQL> select * from t1_a_mv; ID ---------- C1 -------------------------------------------------------------------------------- 1 xxx + some more text here 2 yyy + added text with dbms_lob.append SQL> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews 2 where mview_name='T1_A_MV'; LAST_REF LAST_REFRESH_DA -------- --------------- FAST 270703 12:44:36 SQL> SQL> -- Insert empty_lob then write to it... SQL> set serveroutput on SQL> declare 2 x clob; 3 icnt number; 4 begin 5 insert into t1_a(id, c1) values(3,empty_clob()) return c1 into x; 6 select count(1) into icnt from mlog$_t1_a; 7 dbms_output.put_line('# of rows in the MV LOG, after inserting empty_clob: '||icnt); 8 dbms_lob.write(x,60,1,'Text written to the empty_clob with dbms_lob.write.....................hello!'); 9 select count(1) into icnt from mlog$_t1_a; 10 dbms_output.put_line('# of rows in the MV LOG, after updating with dbms_lob.write: '||icnt); 11 end; 12 / # of rows in the MV LOG, after inserting empty_clob: 1 # of rows in the MV LOG, after updating with dbms_lob.write: 2 PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select * from mlog$_t1_a; ID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ---------- -------------------- ---------- ---------- ---------------- 3 010100 00:00:00 I N FE 3 010100 00:00:00 U U 0400000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ID SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ---------- -------------------- ---------- ---------- ---------------- 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 000000000000 SQL> exec dbms_mview.refresh('t1_a_mv','f'); PL/SQL procedure successfully completed. SQL> -- MV refrehsed OK SQL> select * from mlog$_t1_a; no rows selected SQL> select * from t1_a_mv; ID ---------- C1 -------------------------------------------------------------------------------- 1 xxx + some more text here 2 yyy + added text with dbms_lob.append 3 Text written to the empty_clob with dbms_lob.write.......... SQL> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews 2 where mview_name='T1_A_MV'; LAST_REF LAST_REFRESH_DA -------- --------------- FAST 270703 12:44:37 SQL> SQL> drop materialized view t1_a_mv; Materialized view dropped. SQL> drop table t1_a; Table dropped. SQL> SQL> spool off