ORA-00031: session marked for kill Solution

In this tutorial we will learn how to solve ORA-00031 session marked for kill error.

Step 1. Login in Database with administrator (sys) user and find spid to kill at os level.

SQL> select s.sid, s.username, s.osuser, s.process fg_pid, p.spid bg_pid 
     from v$session s, v$process p where s.paddr = p.addr;	

If you have the user details which find the details as per user name using below query.

SQL> 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='SHRIPAL' and vs.osuser='SHRIPAL';

In my case the output from above query.

---- ---------- --------  ---------   ---------
14    SHRIPAL 	 SHRIPAL  1835:5660    3154

ORA-12560 TNS protocol adapter error

Step 2. Now kill user session at OS level using kill -9 command, you can fire the kill command from ORACLE user or ROOT user.

EX:- kill -9 spid

$kill -9 3154

Kill process at Windows level using orakill command.

EX:- orakill ORACLE_SID spid

If you don’t know the ORACLE_SID, you can find it using:

>echo %ORACLE_SID%
DIGITAL  <<--- oracle_sid in my case

Fire the below query to kill session at os level.

>orakill DIGITAL 3154

So, this is the solution of ORA-00031 error. If you found this article useful please write in the comment box or DM on Instagram.

Chant with me on:

subscribe on youtube

1 thought on “ORA-00031: session marked for kill Solution”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks