drop materialized veiw

when you drop a materialized view, you may get ora-03113 or other error.

here is a work around.

1. find the bad object
select object_name, object_id, object_type, status
from dba_objects

the materialized view is invalid and table is valid.

The problem is that there is still a summary remaining that has the same OBJECT_ID.
2. find the summary

select owner,summary_name,container_owner,last_refresh_date,contains_views,query
from dba_summaries

3. drop the summary
drop summary .;`

4. drop the mview

SQL> col owner for a22
col query for a24 wrapped
select owner,summary_name,container_owner,last_refresh_date,contains_views,query
from dba_summaries SQL> SQL> 2

no rows selected

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> drop MATERIALIZED VIEW ;

Materialized view dropped.

5. if step 4 failed.
SET serveroutput ON
SET echo ON

DECLARE
CURSOR mv_indexes
IS
SELECT 'DROP INDEX '
|| index_name AS stmt
FROM user_indexes
WHERE table_name = 'INSTRUMENT'
AND table_owner = 'P1_OLAP_LEVELONEBANK';
BEGIN
FOR ix IN mv_indexes
LOOP
dbms_output.put_line('Executing: ' || ix.stmt);
EXECUTE immediate ix.stmt;
END LOOP;
END;
/

alter system flush buffer_cache;
alter system flush shared_pool;

the drop MVIEW again.