Andrew Fraser DBA

Materialized Views with dbms_metadata.get_ddl

See the SQL create definition of a materialized view by running this SQL:

set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
spool recreate_sql
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','my_mv_name','my_owner') txt from dual;
spool off
set pages 9999

Look especially for the refresh option, e.g.:

REFRESH FORCE ON DEMAND NEXT null

There are three normally used refresh options:

• Fast = use materialized view logs on source
• Complete = ignore materialized view logs, instead truncate the materialized view and repopulate it from the base tables of source.
• Force = exactly the same as Fast – except when there are no materialized view logs on source, in which case it does a complete refresh.