SQL> 
SQL> /*
DOC>
DOC>Can not currently use "ON COMMIT" with DB link.
DOC>Fast refresh with DB link OK.
DOC>
DOC>*/
SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> 
SQL> create table emp_a as select * from emp;

Table created.

SQL> alter table emp_a add constraint emp_a_pk primary key(empno);

Table altered.

SQL> 
SQL> create materialized view log on emp_a
  2  with rowid(empno, sal, deptno)
  3  including new values;

Materialized view log created.

SQL> conn jyang/jyang@phoenix
Connected.
SQL> 
SQL> drop materialized view mv_emp_a;
drop materialized view mv_emp_a
*
ERROR at line 1:
ORA-12003: materialized view "JYANG"."MV_EMP_A" does not exist 


SQL> -- Can not use "ON COMMIT" with db link
SQL> create materialized view mv_emp_a
  2  build immediate refresh fast on commit
  3  as
  4  select count(*), sum(sal), deptno, count(sal)
  5  from emp_a@athena
  6  group by deptno;
group by deptno
         *
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view 


SQL> -- Fast refresh with db link OK
SQL> create materialized view mv_emp_a
  2  build immediate refresh fast
  3  as
  4  select count(*), sum(sal), deptno, count(sal)
  5  from emp_a@athena
  6  group by deptno;

Materialized view created.

SQL> 
SQL> select * from mv_emp_a;

  COUNT(*)   SUM(SAL)     DEPTNO COUNT(SAL)                                     
---------- ---------- ---------- ----------                                     
         3   20999.99         10          3                                     
         2       6000         20          2                                     
         4       5600         30          4                                     

SQL> conn jyang/jyang@athena
Connected.
SQL> update emp_a set sal=40 where deptno=10;

3 rows updated.

SQL> select count(1) from mlog$_emp_a;

  COUNT(1)                                                                      
----------                                                                      
         6                                                                      

SQL> commit;

Commit complete.

SQL> select count(1) from mlog$_emp_a;

  COUNT(1)                                                                      
----------                                                                      
         6                                                                      

SQL> 
SQL> conn jyang/jyang@phoenix
Connected.
SQL> exec dbms_mview.refresh('mv_emp_a','f');

PL/SQL procedure successfully completed.

SQL> select last_refresh_type from user_mviews where mview_name='MV_EMP_A';

LAST_REF                                                                        
--------                                                                        
FAST                                                                            

SQL> select * from mv_emp_a;

  COUNT(*)   SUM(SAL)     DEPTNO COUNT(SAL)                                     
---------- ---------- ---------- ----------                                     
         3        120         10          3                                     
         2       6000         20          2                                     
         4       5600         30          4                                     

SQL> drop materialized view mv_emp_a;

Materialized view dropped.

SQL> 
SQL> conn jyang/jyang@athena
Connected.
SQL> drop materialized view log on emp_a;

Materialized view log dropped.

SQL> drop table emp_a;

Table dropped.

SQL> 
SQL> spool off