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>;`