SQL Error: ORA-00031: session marked for kill

BS2012

Hi Everyone,

My DB version is

BANNER                                                         
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi 
PL/SQL Release 10.2.0.1.0 - Production                           
CORE 10.2.0.1.0 Production                                         
TNS for Linux: Version 10.2.0.1.0 - Production                   
NLSRTL Version 10.2.0.1.0 - Production                           

If this error message will show up, then is there any other way to kill the session immediately. It has been locked by rollback process. Please suggest something.

The exact error message is mentioned below.

Error starting at line 13 in command:
ALTER SYSTEM KILL SESSION '983,35118'
Error report:
SQL Error: ORA-00031: session marked for kill
00031. 00000 -  "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.

Regards,

BS2012.

Manik

Try unix side killing of process of oracle.. check this link..

http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

Billy Verreynne

The command issued does not actually kill the session. It instead requests the session (likely via the SGA) to terminate - i.e. kill itself/commit suicide.

If the session is stuck waiting on a synchronous kernel call for example, it cannot respond to the kill request. In some cases, this means that as the session is unable to continue (as it is waiting on a synchronous call that will never return), it cannot be successfully instructed to terminate.

In which case the typical action is to instruct the kernel to physically remove the (stuck) executable process instead.

Anirban Mondal

Hi BS2012,

   You can try disconnecting the session. It is available from oracle 9I onwards.

  ALTER SYSTEM DISCONNECT SESSION ('SID','SERIAL#') IMMEDIATE;

  ALTER SYSTEM DISCONNECT SESSION ('SID','SERIAL#') POST_TRANSACTION;

Hope this will help. Please refer to below link for further information on this.

ALTER SYSTEM

Thanks and regards,

Anirban Mondal.