Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

Question

I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is still there. In fact if I try to re-create it I get an error like "object already exists".

I see that there is a table with the same name belonging to another schema. I assume that should not cause the problem, but I felt like mentioning it.

Here is the SQL*Plus output:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 17:01:50 2011

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';  
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL> DROP MATERIALIZED VIEW MY_MVIEW;
Materialized view dropped.

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL> 

I double checked my permission and DROP ALL MATERIALIZED VIEWS is granted to the DI_TEST_AL user.

Answer 1

Connect as sysdba and check if there is any entires in dba_summaries for the MV. If there is run,

drop summary <OWNER>.<MV_NAME>;`

Answer 2

I faced similar issue when i tried to drop materialized view it says View doesn't exist. If I try to create it, it says Name already exists. This is a bug in Oracle.

Before dropping view, query on all_objects table.

SQL : SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME ='MY_MVIEW'; It should return 2 records, ojbect_type = TABLE and Object_type = MATERIALIZED VIEW.

But if its showing only one record with Object_type = Table then drop that table from database

Drop table MY_MVIEW;

And try to re-create the materialized view. It should work.