1. Find out thread id that needed to be kill from OS level
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr;
If you know the user detail you can filter the query.
select vs.sid,vs.username,vs.osuser, vs.process,vp.spid
from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.username='NAZMUL'
and vs.osuser='ENAZMUL';
Output:
SID USERNAME OSUSER PROCESS SPID
---- --------------- ---------- --------- ---------
10 NAZMUL ENAZMUL 1965:6969 5292
2. Kill the session from OS level
Unix:
Run in UNIX as the oracle/root user
kill –9 spid
Example from the above output:
kill -9 5292
Windows:
orakill ORACLE_SID spid
ORACLE_SID is the unique database name.
Example from the above output:
orakill cor 5292
Here cor is the unique database name.