删除物化视图hang住

有用户要求删除物化视图，相关session卡住：drop MATERIALIZED VIEW scm.FW_NC_MAT;

select t.EVENT,t.sql_id,t.* from v$session t where status='ACTIVE' and schemaname='SYS' and type<>'BACKGROUND';

发现等待事件为enq: JI - contention

查询等待的ojbect FW_NC_MAT 的session:

SELECT sess.SID, sess.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.LOCKED_MODE, ao.OBJECT_NAME
FROM gV$LOCKED_OBJECT lo,
     gv$session sess,
     dba_objects ao
WHERE lo.SESSION_ID = sess.SID
  AND ao.OBJECT_ID = lo.OBJECT_ID;

SID    SERIAL# ORACLE_USERNAME                OS_USER_NAME                   LOCKED_MODE OBJECT_NAME
---- ---------- ------------------------------ ------------------------------ ----------- --------------------------------------------------------------------------------
2364      57819 SCM                            oracle                                   3 FW_NC_MAT
2364      24911 SCM                            oracle                                   3 FW_NC_MAT

查询spid:

select p.spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=2364

kill相关session,物化视图顺利删除