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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply