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.:


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.
April 7, 2010

  • Leave a Reply

    Your email address will not be published. Required fields are marked *