Killing Blocking Sessions

Finding Blocking sessions:
----------------------------------

Views:
---------
v$session
v$lock
dba_blockers
dba_waiters

1.From v$session:
SQL> select  blocking_session,  sid,  serial#,  wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by  blocking_session;


2. From v$lock
SQL> select * from v$lock;

###If attribute block=1 in v$lock then the Sid relating to that row is blocking session. we can also find which session is waiting by comparing the ID1 and ID2 attributes of the v$lock. If ID1 and ID2 have same values with blocking and other session. Then session other than blocking session is waiting session.###


3. From dba_blockers
SQL> select * from dba_blockers;

###From the above query we will get session id of the particular blocking session.###


4. From dba_waiters
SQL> select holding_session, blocking_session from dba_waiters;

###From the above statement, holding session is the blocking session and waiting session is the one which is waiting.we will get session id's from here.###



Finding which user blocking or waiting using sid information and view v$session:

###using sid we get from v$session or v$lock or dba_blockers or dba_waiters, we can find particular user who is blocking.###

SQL> select sid, username, serial# from v$session where sid=##sid we get form above views##;



Finding which particular SQL is blocking or waiting from the views v$session and v$SQL:

### After getting Sid which is blocking or waiting sessions. using that particular sid we can get sql_id relating to that sid from v$session. Once we get the sql_id we query the view v$SQL for SQL_text Attribute for particular SQL which is blocking.###

SQL> select sid, username, serial#, sql_id from v$session where sid='   ';

SQL> select sql_text, sql_id from v$SQL where sql_id='#got from above query#':



killing the blocking session:
--------------------------------------------

SQL> Alter system kill session 'SID,Serial#';