Monday, January 30, 2012

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

No comments:

Post a Comment