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.
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;