title: ORACLE - Creating view from materialized views
body: <p>I need to change quite a huge amount of materialized views to usual views on Oracle 11g.
  My first approach:</p>
  
  <p>1.) Get the DDLs</p>
  
  <pre><code>select dbms_metadata.get_ddl(replace(object_type, ' ', '_'), object_name, owner)
  from all_objects
  where owner = 'MY_SCHEMA'
  and object_type = 'MATERIALIZED VIEW'
  and object_name in (
  'MAT_VIEW1'
  ,'MAT_VIEW2'
  ,'MAT_VIEW3'
  ,'MAT_VIEW4'
  -- ..
  ,'MAT_VIEW100'
  ); 
  </code></pre>
  
  <p>Each row contains the DDL for the corresponding MVIEW, f.e.:</p>
  
  <pre><code>  CREATE MATERIALIZED VIEW "MY_SCHEMA"."MVIEW1" ("ROW1", "ROW2", "ROW3")
    ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 
    NOCOMPRESS NOLOGGING
    STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MY_TABSPACE" 
    BUILD IMMEDIATE
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MY_TABLESPACE" 
    REFRESH FORCE ON DEMAND
    WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
    USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
    AS SELECT ROWA AS ROW1,
    ROWB AS ROW2,
    ROWC AS ROW3
    FROM MY_SCHEMA.TABLE1
  </code></pre>
  
  <p>2.) Get rid of the additional technical configuration by using a regular expression. For convenience I use RegexBuddy and copy paste the text. Oracle doesn't support Look Forward/Backward features as fas as I noticed: </p>
  
  <pre><code>(?=ORGANIZATION\sHEAP).+?(QUERY\sREWRITE)
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>  CREATE MATERIALIZED VIEW "MY_SCHEMA"."MVIEW1" ("ROW1", "ROW2", "ROW3")
    AS SELECT ROWA AS ROW1,
    ROWB AS ROW2,
    ROWC AS ROW3
    FROM MY_SCHEMA.TABLE1
  </code></pre>
  
  <p>At last I removed "MATERIALIZED" from each the DDL, now I have the valid DDLs for the views.</p>
  
  <p>Meanwhile I got a spontanious idea and just tried</p>
  
  <pre><code>create view usual_view1 as
  select * from mat_view1;
  </code></pre>
  
  <p>Hm, that worked as well, with quite less effort.</p>
  
  <p>So my question: is there any difference in the end when creating views from the definition of a <strong>materialized</strong> view in comparision to the usual way of creating it by DDL?</p>