The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:
SELECT w.session_id waiting_session , h.session_id holding_session , w.lock_type --, h.mode_held --, w.mode_requested , ( SELECT module || ' :: ' || action FROM v$session WHERE sid=h.session_id ) holding_module_action , ( SELECT module || ' :: ' || action FROM v$session WHERE sid = w.session_id ) waiting_module_action FROM ( SELECT /*+ NO_MERGE */ * FROM dba_locks ) w , ( SELECT /*+ NO_MERGE */ * FROM dba_locks ) h WHERE (( (h.mode_held != 'None') AND (h.mode_held != 'Null') AND ( (h.mode_requested = 'None') OR (h.mode_requested = 'Null') ) ) AND (( (w.mode_held = 'None') OR (w.mode_held = 'Null') ) AND ( (w.mode_requested != 'None') AND (w.mode_requested != 'Null') ) ) ) AND w.lock_type = h.lock_type AND w.lock_id1 = h.lock_id1 AND w.lock_id2 = h.lock_id2 AND w.session_id != h.session_id ;
..or you can try the following:
SELECT NVL(s.username, '(oracle)') AS username , s.sid , s.serial# , s.osuser , s.machine , s.program , s.module , o.object_name , sq.sql_text , TO_CHAR(s.logon_time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v$locked_object l , dba_objects o , v$session s , v$process p , v$sql sq WHERE l.object_id = o.object_id AND l.session_id = s.sid AND s.paddr = p.addr AND s.sql_address = sq.address ;
Locking sessions can be killed using the following SQL command, using the SID and SERIAL# from the query above:
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;