Feb 242014

I dropped a materialized view (without the preserve table option), but the object still exists! Here’s a copy and paste from my session:

drop materialized view gone_for_good;

materialized view GONE_FOR_GOOD dropped.

select or_is_it
from   gone_for_good;


-- covering off the easy option, 
-- there isn't a (public) synonym
-- with the same name:
select *
from   all_synonyms
where  synonym_name = 'GONE_FOR_GOOD';

no rows selected

How did this happen? Answers in the comments!

  2 Responses to “SQL Quiz: I dropped this materialized view, but can still select from it! How?”

  1. sokrates@11.2 > create table gone_for_good as
    2 select 'Nope!' as or_is_it from dual;

    Table created.

    sokrates@11.2 > create materialized view gone_for_good
    2 on prebuilt table as
    3 select 'Nope!' as or_is_it from dual;

    Materialized view created.

    sokrates@11.2 > drop materialized view gone_for_good;

    Materialized view dropped.

    sokrates@11.2 > select or_is_it
    2 from gone_for_good;


 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>