[Oracle11g] Materialized View Drop Statement Timeout Issue

Issue:  Unable to Drop Materialized View (Session Timeout).

Description:  DROP Statement not working and session getting timed out

Resolution:

1.       Check whether locks exist on Materialized Views in OLAP Database using the following query:

SELECT LCK.SID , LCK.TYPE,  GVSES.SERIAL#

FROM

V$LOCK LCK,

GV$SESSION GVSES

WHERE

LCK.SID =GVSES.SID(+)

AND LCK.TYPE='JI' 

Note:  V$LOCK – A view lists the locks currently held by the Oracle Database.

GV$SESSION – A view containing Session info.

               

 TYPE Column in V$LOCK implies Type of Object on which Lock exists ‘JI’ is for Materialized View Locks.

Other lock types are as follows: JI (Materialized view)
                                                   TM (DML or Table Lock)
                                                   TX (Transaction)

2.    Get SID and SERIAL# from the first query and run the following query on the database.

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERVICE#' IMMEDIATE;

SID and SERVICE # will be replaced with values from first query.

 Example:  ALTER SYSTEM KILL SESSION '159,87' IMMEDIATE;

3.       After killing all locks on Materialized Views run the DROP Statement again. Worked for us.