Ora-00031 session marked for kill

Today we face a problem on our production database.

problem is :- One of user's sesssion is killed by "alter system kill session" but that session was being

a huge DML operation.

after this command that session marked for kill .

by this time, we fine that this session is blocker of approximate 1200 session.

and system is about hang.

In that situation we killed that session from OS. and it solve our problem.

Ora-00031 session marked for kill

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

1. To find out the session what need to killed

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program

FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'

And s.sid=4314 -- ( select blocking_session from v$active_session_history )

2. For killing the session normaly

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3. if it is DML transaction session then it need to be rollback

what is so time consuming .

(To see the kill session (rollback status )

SQL> select used_ublk from v$transaction;

If USED_UBLK > 0, the session is in the process of being rolled back.

If USED_UBLK is 0, the session is effectively KILLED, resources have been released, we are just

waiting for their client to send a request so we can tell them "you have been killed". Locks are

gone at this point, its rolled back.

If you see used_ublk > 0 (say 1000) and 15 seconds later its at 800, you can guess that it will

take about another minute (15 seconds/ 200 blocks, 800 more to go) to finish rolling back.

But you need kill the process immediately

You need to kill the session’s process from OS level

------For seeing the spid (process id) to kill the process in OS level

select spid

from v$process a , v$session b

where a.addr=b.paddr

and b.sid=4314

------OS kill statemente

=========================

on windows

---------

C:> orakill ORACLE_SID spid

On Unix

---------

$ kill spid

$ kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

$ ps -ef | grep ora