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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s