How to remove lock on a table. ERROR at line 1: ORA-00031: session marked for kill;

What is ORA-00031: session marked for kill , ALTER SYSTEM KILL SESSION, resource busy and acquire with NOWAIT

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