Problem with dropping materialized views and mv logs taking a long time (or never) to complete

Question

Apologies if it's a simple question, DBA is not my main area of expertise!

I have a materialized view that's fast refreshable, joining two tables.

When I try to drop either the MV itself or logs associated with constituent tables, it starts the process but never comes back.

I have tried truncating the log tables and MV table, and then dropping the MV, but it's the same.

There's an entry in dba_summaries for the MV.

Any clues as what is stopping Oracle from dropping it?

EDIT even this doesn't execute / return :

ALTER MATERIALIZED VIEW  STAGING_OWNER.CONTRACT_SUMMARY_MV_FR  REFRESH COMPLETE;

Version information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Answer 1

You can do:

truncate table STAGING_OWNER.CONTRACT_SUMMARY_MV_FR ;

This simply will flush the MV. Then:

dbms_mview.refresh('STAGING_OWNER.CONTRACT_SUMMARY_MV_FR',method=>'C',atomic_refresh=>FALSE);

this will speed up mview rebuild. If you still experience any troubles you must trace event 10053 to understand why refresh is taking so much time.