Q
Problem solve Get help with specific problems with your technologies, process and projects.

Problem dropping materialized views

I have an issue with dropping materialized views. I've been trying to DROP an MV which is defined as REFRESH ON DEMAND and there are no locks anywhere. A DROP statement sits there and never returns.

I have an issue with dropping materialized views. I've been trying to DROP an MV which is defined as REFRESH ON DEMAND and there are no locks anywhere. A DROP statement sits there and never returns. These MVs themselves are marked as INVALID. But recompiling them never returns. The underlying SELECT is based on ONE SOURCE table. This has been happening with a few other MVs as well.

How do I go about dropping these offending MVs without bouncing the database? Is there some sort of a FORCE DROP?...

I am just helpless here with our DBA giving up on this completely. Thanks.

There is no FORCE option with the DROP MATERIALIZED VIEW command. One suggestion is to drop any MATERIALIZED VIEW LOG first, if it exists. Otherwise, you'll have to figure out what is holding you up. Querying V$LOCK as described in my white paper "Solving User Problems" on my Web site can help you determine what the holdup is. If the command is not waiting on any lock to be released, then looking at V$SESSION_WAIT and V$SESSION_EVENT can point to the holdup. This is described in the same white paper.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close