SQL Snippets ™: Tutorials for Oracle Developers

If for some reason we need to prevent refresh operations of any sort, FAST or COMPLETE, on our materialized views we can use the NEVER REFRESH method.

create materialized view mv
  NEVER REFRESH
  as select * from t
;

select * from mv ;
 

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 

Let's see what happens when we update the base table and then attempt a refresh.

update t set val = upper(val) ;
commit ;

execute dbms_mview.refresh( 'MV' );
BEGIN dbms_mview.refresh( 'MV' ); END;

*
ERROR at line 1:
ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view ("MV")
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 1


 

Oracle prevented the refresh by raising an error.

I cannot see a practical reason for having a materialized view with NEVER REFRESH set at all times. (If you know of any please let me know using the Comments link below.) NEVER REFRESH can come in handy though when refresh operations on a materialized view need to be prevented temporarily during maintenance or debugging operations. In this case the materialized view's refresh mode can be changed to NEVER REFRESH using the ALTER MATERIALIZED VIEW command.

Cleanup

drop materialized view mv ;

update t set val = lower(val) ;
commit ;