Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, January 23, 2011

Alter system kill session and ORA-00031: session marked for kill

Today we face a problem on our development database.
problem is :- One of user's sesssion is killed by "alter system kill session" but that session was being a huge DML operation with the wait "HS Message sent to agent".

after this command that session marked for kill .
by this time, we found 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
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




SQL@ homedev> select spid
  2  from v$process a , v$session b
  3  where a.addr=b.paddr
  4  and b.sid=155
  5  /

SPID
------------
8132

D:\home_Backup\bas_user_log>orakill HOMEDEV 8132

Kill of thread id 8132 in instance HOMEDEV successfully signalled.

On Unix
---------
$ kill spid
$ kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
$ ps -ef | grep ora

No comments: