Check 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; 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 l2.id2 = l2.id2;
View sessions details
select sid, to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time, username, type, status, process, sql_address, sql_hash_value from v$session where username is not null and sid = &sid_no and serial#=&serial_no;
Kill blocking session
alter system kill session '&SID,&SERIAL '; or ALTER SYSTEM DISCONNECT SESSION '&SID,&SERIAL' IMMEDIATE;