ORA-00031: session marked for kill


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

Column

Description

SID

Session Identifier

USERNAME

Oracle USERNAME

ODUSER

Operating system client user name

PROCESS

Operating system client process ID

SPID

Operating system process identifier


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.

Comments