select ses.SID, substr(SQ.SQL_TEXT,0),sq.LAST_LOAD_TIME,
ses.schemaname,
SES.LOCKWAIT,
SES.BLOCKING_SESSION_STATUS,
BLOCKING_SESSION,SES.STATE,
SES.EVENT,
SES.EVENT#,
SES.SECONDS_IN_WAIT
,(select object_name from dba_objects where object_id=ses.ROW_WAIT_OBJ#) object_name
from V$SESSION SES inner join V$SQL SQ on
SES.SQL_ID=SQ.SQL_ID
order by sq.LAST_LOAD_TIME desc
It turned out every session was contending for the same object. Furthermore, the the following query result showed they were requesting exclusive lock but none succeeded.
Request /Lmode = 6 --> exclusive lock
Block = 1 --> Blocker
Block = 0 --> Blockee
SELECT sid, type, id1, id2, lmode, request,block FROM V$LOCK WHERE request < 0 order by sid;
I looked into procedure that upserts that specific objects, and found that before insert it places an exclusive lock explicitly by:
Lock table xxx in exclusive mode.
Yes, that's the reason why the program hung.