Sessions Marked for Kill

Some times when we kill an ORACLE session with the “alter system kill” statement, it will return a message that the session is marked for kill. In most of the cases, it will be rolling back a huge transaction that you executed.

As an example, if you killed a session which was supposed to update/delete 1Million rows on half the way , it will definitely take some time to roll the changes back. This can be verified by checking the v$transaction table.

SQL> select count(*) cnt from test_tab1;
     
CNT
----------  

769538

SQL> update test_tab1 set c1 = c1+1;

Huge update is going on now.In between, if you kill the session and check the v$transaction table you will see the change in the values of "Number of undo blocks used" and "Number of undo records used"

SQL> alter system kill session '49,27133';
alter system kill session '49,27133'
*
ERROR at line 1:
ORA-00031: session marked for kill


SQL> select status
2  from v$session
3  where sid = 49;


STATUS
--------

KILLED


SQL> select used_ublk,used_urec
2  from v$transaction
3  where ses_addr=(
4                  select saddr
5                  from v$session
6                  where sid = 49
7                  );


 USED_UBLK  USED_UREC
---------- ----------  

 24769     161040


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

24081     155939


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

22605     145321


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

10487      66038


SQL> /
 USED_UBLK  USED_UREC
---------- ----------    

6637      41384


SQL> /

no rows selected

The rollback is over. We can check V$SESSION now.

SQL> select status
2  from v$session
3  where sid = 49;

no rows selected