This content has been marked as final. Show 10 replies
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.
So according to the error description,you need to wait for some time.It should go away.
You did not provide your OS but the best way to kill a session is to find the process id of the session and kill it from the operating system. After the os process is killed PMON cleans up the session.
Especially in highly concurrent systems I have not been able to kill a session using 'alter system kill session'. It marks the session to be killed, just like your case does, and keeps it for several hours in that situation.
Killing the os process always worked for me.
Ya thats right, before killing any sql session, get the spid (pid) of that sid and after killing the session if it show that "Session marked to kill" then you can kill the process id at os prompt.
i am waiting for 4 hours!
but it does not go away..
i am on unix .. how can i find the os level process id?
i dont want to accidentally kill pmon or any other process :)
how can i be sure? any help would be appreciated.
Check out V$process view.It has PROCESS column which is the o/s process for the session connected.You can kill that session.
As you are on Unix killing the Operating system process will cause process recovery to cleanup the database process quicker.
thats what resides in my v$session is : 5364:5792
but i cant find any processes like 5364 or 5792 on os?
ps -ef|grep 5364 and 5792 returns nothing!
Look at v$process and spid run the sql below:-
set linesize 120
col sid for 999
col username for a14 trunc
col osuser for a18 trunc
col spid for 99990
col logon_time for a12
col status for a9 trunc
col machine for a26 trunc
col running for a10 trunc
, p.spid spid
, to_char( logon_time, 'Mon dd@hh24:mi') logon_time
, rtrim (s.module)||decode( nvl(length( rtrim(s.module)),0),0,'',' ')|| upper(s.program) running
from v$session s
, v$process p
where ( p.addr = s.paddr ) and s.type!='BACKGROUND'
and upper(s.program) not like '%CJQ0%' and s.program is not null and s.username is not null
order by s.sid;
ok, i got the session id with this query!!!
thank you so much!!
Glad to help mate