Wednesday, February 8, 2017

Find Blocking Session details

How to find Oracle Database Blocking Session Details
In first step, find SID from v$session.

SQL> select process,sid, blocking_session from v$session where blocking_session 
 is not null;

 ———— ———- —————-
 1234 365 366
 1234 366 365

In second step find the serial number for the Blocking Session to kill using SID
 SQL> select SERIAL# from v$session where SID=365;

In third step, kill the blocking session using SID and serial number

SQL> alter system kill session ’365,130′;
 System altered.

Handle Blocking sessions for oracle database

For blocking session most important is to find out which session is getting blocked and which is holding the session. most of the time seeing one session is blocking many other session so to handle this type of situations use this method to clear the blocks. steps: 1) select instance_name||' - '||status||' - '||startup_time||' - '||host_name||' - '||sysdate from v$instance; to confirm the db name are you in correct database. 2) select sid from v$lock where block=1; this query gives you the sid's which are getting effected. 3) find the sql text for this sessions. select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid'); 4) check for the other detials like their sid, serail#, osuser, machine and their status ( Active / Inactive) by passing the sid from previous query of step 2. check for all sid's from all of the results you may not get any sql text with one sid which will be active in status that is the one main culprit blocking session which is holding lock for other sessions to execute. select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time from v$session where sid=&123; Identify the holder session which is active for more confirmation you can also check the holders and waiters. check holders & waiters: ======================== set pagesize 100 select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from v$lock where (id1, id2, type) IN (SELECT id1, id2, type from v$lock where request>0) ORDER BY id1, request; This query results with more details the top one is the holder and others are waiters. which is notthing but the active session which you can see with the pervous query. 5) kill the holder session ALTER SYSTEM KILL SESSION '&sid, &serial'; or run this. SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ''';' a, 'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID, 'kill -9 ' || p.SPID FROM gv$session s, gv$process p WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id)) AND s.sid = &sid; you will get the result like alter system kill session '123, 32422'; ps -ef |grep LOCAL=NO|grep 234223 kill -9 234223 use any one of the result to clear the lock. Most of the time a DBA  can not directly kill the locks, in this situation consult with the application team to clear the lock and use this easy steps to clear the lock.

No comments:

Post a Comment