Example: Who Is Holding a Lock that Other Sessions Need?
The following query shows which sessions on the local VNODE are holding locks where other sessions are waiting:
update ima_mib_objects
set value = DBMSINFO('IMA_VNODE')
where classid = 'exp.gwf.gwm.session.control.add_vnode'
and instance = '0'
and server = DBMSINFO('IMA_SERVER');
select distinct
resource_id,
lock_id,
lock_state,
ima_locklists.locklist_id,
locklist_server_pid,
locklist_session_id,
effective_user,
db_name,
session_terminal
from
ima_locks,
ima_locklists,
ima_server_sessions
where resource_id in
(
select distinct
ima_locks.resource_id
from
ima_locks
where lock_state != 'GR'
)
and lock_state != 'WT'
and ima_locks.locklist_id = ima_locklists.locklist_id
and ima_locklists.locklist_server_pid = ima_server_sessions.server_pid
and ima_locklists.locklist_session_id = ima_server_sessions.session_id;