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