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.

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
share|improve this question
    
can you post your version including patchset? –  Jack Douglas Oct 13 '11 at 15:53
    
@Jack Douglas Thanks, please see the post above, I've updated version info. –  Ashkan Aryan Oct 14 '11 at 8:37

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

share|improve this answer

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.