Materialized Views


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
  as select * from t

select * from mv ;
       KEY VAL
---------- -----
         1 a
         2 b
         3 c

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.


drop materialized view mv ;

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

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=]SQL Snippets: Materialized Views - NEVER REFRESH[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="">SQL Snippets: Materialized Views - NEVER REFRESH</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - NEVER REFRESH
  • URL (href):