Problem
Description: DBA wanted to kill a
long running oracle job/process and he issued ALTER SYSTEM KILL SESSION ‘SID,
serial#’ command. DBA got a message ORA-00031: session marked for kill. But,
after killing the job, still he is not able to modify the table which was
involved in the job or not able to add an index to the table. When he tries do
perform any kind DDL change on the table, getting resource busy and acquire
with NOWAIT error message.
Session cannot be killed immediate because the session is
involved in a non-interruptible operation. Example, the transaction is getting
rolled back or being blocked by a network operation.
Why Session
Marked for Kill?
I have a
transaction table of 10 million records and I started batch job which
update/manipulate the transaction table. The batch process takes 1hour and due
to some reason I had to kill the job. I issued ALTER SYSTEM KILL SESSION ‘SID,
serial#’ and I got a message ORA-00031: session marked for kill.
Normally, huge table manipulation transactions required lot of oracle resources
like redo to be used and in the middle of the transaction if you try to kill
the process, oracle will take its own time to roll back the transaction. In
such situations you get ORA-00031: session marked for kill message
and in the background the rollback will start by oracle. The speed of the
transaction rollback again depends on the system resources. Until the
transaction fully get rolled back the status of the particular session in
v$session dictionary view would be KILLED but the table will
be locked by the killed session. If you try to do any kind of DDL operation on
this table from different session you will get resource busy and
acquire with NOWAIT message.
How to Monitor the
Transaction Rollback?
Suppose I have session
running with sid 14 and serial#787 with and data purge delete on the
transaction table with 10million records.
To monitor the
undo generated from v$transaction table. Please find the below mentioned query.
SQL> select used_ublk from
v$transaction;
USED_UBLK
----------
5650
1 row selected.
It means, these much
undo is generated.
I am going to kill the
session using alter system kill session command.
SQL>alter system kill session '13,787';
Alter system kill session '13,787'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select username, status from
v$session where sid=13;
SID STATUS
-------------------- ----------
13 KILLED
1 row selected.
Again if you check the
v$transaction table you can see the difference in value
SQL> select used_ublk from
v$transaction;
USED_UBLK
----------
5298
1 row selected.
SQL> /
USED_UBLK
----------
4302
1 row selected.
SQL> select username, status from
v$session where sid=13;
SID STATUS
--------------------
----------
13 KILLED
1 row selected.
SQL> select used_ublk from
v$transaction;
USED_UBLK
----------
1349
SQL> /
USED_UBLK
----------
502
One thing we can
notice that the used_ublk is coming down and the v$session status remains as
KILLED.
SQL> /
no rows selected
This means the oracle
finished it rolling back activity.
SQL> select
username, status from v$session where sid=13;
no rows selected
This means the session
removed from v$session view after the full rollback of the transaction.
Manual steps to
release the lock on the table
In some cases the
rolling back process will take long time may be for hours. In such situations
if you wanted to perform any kind of update/insert /DDLs the table will not
allow due to the lock by the killed session. You will get an error message
says ORA-00054 resource busy and acquired with NOWAIT specified. In
case if you don’t want to wait for long time until the rollback completes,
there are some workaround to terminate the lock by killing process from OS
side. You can use kill (UNIX) ORAKILL (WINDOWS) to
terminate the process. This is not recommended.
UNIX
Select p.spid from v$session s ,
v$process p
where p.addr =s.paddr
And s.sid=<> and serial#=<>;
$ kill <spid>
WINDOWS
Select p.spid from v$session s ,
v$process p
where p.addr =s.paddr
And s.sid=<> and serial#=<>;
Then use orakill at the DOS
prompt:
c:>orakill
sid spid
