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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 :


Version information:

Oracle Database 10g Enterprise Edition Release - 64bit
PL/SQL Release - Production
CORE  Production
TNS for Linux: Version - Production
NLSRTL Version - 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

You can do:


This simply will flush the MV. Then:


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


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.