SELECT
s.inst_id,
p.spid process_id,
a.oracle_username,
a.os_user_name,
a.locked_mode,
b.object_name,
b.object_type,
s.sid,
s.serial#,
s.program
from
gv$locked_object a, dba_objects b, gv$session s, gv$process p
where
a.object_id = b.object_id AND
a.process = s.process AND
a.SESSION_ID = s.SID AND
p.addr = s.paddr AND
p.inst_id = s.inst_id


In a cluster database use gv$ instead of v$ !


alter system [disconnect|kill] session 'sid,serial#,@inst_id' immediate;


kill locked sessions
SELECT 'alter system kill session '''||b.sid||','||b.SERIAL#||''';'
FROM v$lock a, v$session b, dba_objects o1, dba_objects o2
WHERE a.SID = b.SID
AND a.id1 = o1.object_id (+)
AND a.id2 = o2.object_id (+)
AND b.blocking_session IS NOT NULL
AND username IS NOT NULL

query blocking sessions:
=======================================================
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

=======================================================
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2 ;

=======================================================
select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;http://www.rocworks.at/wordpress/?p=446