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.
