Nov 182013
 

Something I spotted on the Oracle-L mailing list the other day:

drop materialized view 
  and_keep_the_results_you preserve table;

For a script, keep reading.

create table to_aggregate as
  select round(dbms_random.value(1, 100)) 
             of_this, 
         mod(rownum, 10) this
  from   dual
  connect by level <= 100;

create materialized view 
    and_keep_the_results_you as
  select this, sum(of_this)
  from   to_aggregate
  group  by this;

-- now you see it
select count(*)
from   user_mviews
where  mview_name = 
           'AND_KEEP_THE_RESULTS_YOU';

drop materialized view 
  and_keep_the_results_you preserve table;

-- now you see now you don't
select count(*)
from   user_mviews
where  mview_name = 
           'AND_KEEP_THE_RESULTS_YOU';

-- but the data are still there!
select * 
from   and_keep_the_results_you;

  One Response to “How Do You Drop a Materialized View Without Losing the Computed Data?”

  1. I have checked your blog and i’ve found some duplicate
    content, that’s why you don’t rank high in google’s search results, but there is
    a tool that can help you to create 100% unique articles, search for:
    Boorfe’s tips unlimited content

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)