How to find blocking session and kill the session from database
Posted by vasselva on November 5, 2007
During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.
* To find blocking session jobs below query will useful. It will return two rows.
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
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
alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ‘365,130′;
System altered.
Deepak Bansal said
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
select process,sid, blocking_session from v$session where blocking_session is not null
*
ERROR at line 1:
ORA-00904: “BLOCKING_SESSION”: invalid identifier
vasselva said
Hi Deepak,
Thanks for your comments. SQL works fine for me. Might be some other issue in your database.
yogendra said
This sql is for 10g version of oracle. It does not work for 9i.