Blocking Session

* To find blocking session jobs below query will useful. It will return two rows.

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

PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365

* Second step to find the serial number for the Blocking Session to kill

SQL> select SERIAL# from v$session where SID=<SID number>
E.g.

SQL> select SERIAL# from v$session where SID=365;

SERIAL#
———-
130

* Final step to kill the blocking session

SQL> alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ’365,130′;
System altered.