Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
what is DO_OPP_SEARCH_MVIEW? –  Jack Douglas Aug 1 '11 at 16:45
    
If you want to drop the MY_MVIEW materialized view, drop it like so: DROP MATERIALIZED VIEW MY_MVIEW. ATM, you're dropping something else, and it might be critical... –  Kerri Shotts Aug 1 '11 at 19:29
1  
I'm trying to drop MY_MVIEW, I changed the name in the example for brevity but I forgot that bit... Any idea how to debug this? Why oracle says it's dropped but it's not? Cheers –  spaceCamel Aug 2 '11 at 9:15
    
Do you have a support contract? there is some 10g bugs which cause what you are seeing, perhaps they are stil around. I tried to replicate against 11gR2 but was unable to. Perhaps provide a complete script from initial table and MV which others can try to replicate. –  Matthew Watson Aug 2 '11 at 13:33
    
Try to include STATUS in the select on ALL_OBJECTS. I have also this problem, and for me, status on the materialized view is INVALID. –  awe Apr 17 '13 at 6:29
add comment

1 Answer

up vote 3 down vote accepted

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>;`
share|improve this answer
    
Thanks mate, that was preventing the materialized view to be removed completely... –  spaceCamel Aug 3 '11 at 9:20
    
I have this exact problem, and the MV was listed in dba_summaries. The problem is when I try to run DROP SUMMARY MY_OWNER.MY_MATERIALIZED_VIEW;, I get error ORA-00950 ... invalid DROP option. –  awe Apr 17 '13 at 6:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.