kill blocking session from database

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.