ORA-00031: session marked for kill -ZOMBIE SESSIONS ORACLE
In oracle, when we kill a session from database , sometimes due to configuration of recursive application module, sessions get spawn or stay as zombie process and not terminate
kish@exdbx<>!oerr ora 00031
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.
kish@exdbx<>update SALES_TAB_COPY_TEMP set NULL_COLUMN = 'ABC';
update SALES_TAB_COPY_TEMP set NULL_COLUMN = 'ABC'
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
When the session is killed from database, we see ora-00031 message
kish@exdbx<>alter system kill session '60,7';
alter system kill session '60,7'
*
ERROR at line 1:
ORA-00031: session marked for kill
After kill of session from database, still the session rollback very slow on database
kish@exdbx<>select username,status from v$session where status='KILLED';
USERNAME STATUS
------------------------------ --------
TEST KILLED
In order to kill the parent process to avoid spawn of child process, try to identify the spid to kill from OS level
kish@exdbx<>select p.spid from v$process p
2 inner join v$session s on p.ADDR = s.PADDR
3 where s.username='TEST' and s.status='KILLED';
SPID
------------------------
6483
Kill the OS process
[oracle@exdbadm01 ~]$ ps -ef|grep 6483
oracle 6483 6359 3 May21 ? 00:01:56 oracleexdbx1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 10751 9047 0 00:37 pts/1 00:00:00 grep 6483
[oracle@exdbadm01 ~]$ kill -9 6483
Verify the killed process
kish@exdbx<>select username,status from v$session where status='KILLED';
no rows selected