It sounds like you are dropping the underlying table, not the materialized view. If you have a materialized view my_mv, there is also a table called my_mv.
You need to drop the materialized view by from the Materialized View node in the object browser.
1 person found this helpful
When I did this, since SQL Developer had these MVs in among the tables, it tried to run a "DROP TABLE" statement rather than a "DROP MATERIALIZED VIEW" and an ugly error message came along with that and of course it failed.
No - Sql Dev did NOT have MVs 'in among the tables' - the tables node ONLY has tables.
See the 'CREATE MATERIALIZED VIEW' section of the Oracle docs for the explanation of the other responder's reply:
When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data.
Do this simple test:
1. examine the list of tables for a table named 'ABC'
2. examine the list of MVs for one named 'ABC'
3. create an MV named 'ABC'
If you reexamine both lists you will find an MV named 'ABC' in the materialized view node and a table named 'ABC' in the tables node.