Check the Lock, Blocking & deadlock in Oracle database
Quick way to find out the blocking at instance level:
select *fromV$lock where block >0;
How long a blocking session is blocking by other sessions:
select blocking_session, sid, serial#, wait_class, seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;
Query shows the blocking session as well as all the blocked sessions
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;
Check the Status of blocker or waiter
Select sid ,decode(block,0,'NO','YES') Blocker ,decode(request ,0,'NO','YES')WAITERfrom v$lock where request>0 or block>0 order by block desc;
Check the Query involved causing blocking
select sid, sql_text
from v$session s, v$sql q
where sid in(68,81)and( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id );
Check the status of session is active or inactive
SQL> select sid,serial#,USERNAME,status from v$session where sid in(4284);SIDSERIAL# USERNAMESTATUS------------------------------14707233APPSINACTIVE189548684APPSACTIVE
Kill the inactive session with following command
alter system kill session '1470,7233' immediate;
Note: If you got the following message : session-marked-for-kill. Follow the link to check the transaction status:
ORA-00031 Session Marked For Kill
If multiple session is caused blocking then need to kill multiple session use the following scripts:
Script to kill multiple sessions
select 'alter system kill session '''|| sid ||','||serial# ||''' immediate;'from v$session where sid in(1645,1757,2100,827,1678,1609,1653,1640,1730);
Check the Lock, Blocking & deadlock in Oracle database
Quick way to find out the blocking at instance level:
select * from V$lock where block > 0;
How long a blocking session is blocking by other sessions:
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session;
Query shows the blocking session as well as all the blocked sessions
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;
Check the Status of blocker or waiter
Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;
Check the Query involved causing blocking
select sid, sql_text from v$session s, v$sql q where sid in (68,81) and ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id );
Check the status of session is active or inactive
SQL> select sid,serial#,USERNAME,status from v$session where sid in (4284); SID SERIAL# USERNAME STATUS ----- ------- -------- ---------- 1470 7233 APPS INACTIVE 1895 48684 APPS ACTIVE
Kill the inactive session with following command
alter system kill session '1470,7233' immediate;
Note: If you got the following message : session-marked-for-kill. Follow the link to check the transaction status:
ORA-00031 Session Marked For Kill
If multiple session is caused blocking then need to kill multiple session use the following scripts:
Script to kill multiple sessions
select 'alter system kill session '''|| sid ||','||serial# ||''' immediate;' from v$session where sid in (1645, 1757,2100,827,1678,1609,1653,1640,1730);