Tag Archives: ORA-00031

ORA-00031: session marked for kill in Oracle

Checked the Kill Session in Oracle

Rollback the transaction which is running or killed the transaction before completion.

ORA-00031: session marked for kill

Very Huge table transactions required lot of oracle resources like generating lot of redo. In the middle of the transaction if you try to kill the process, oracle will take its own time to rollback the transaction by applying all changes back and make a consistent state.

Example:

Monitor the Rollback Transaction:

Suppose, I have session running with sid 13 and serial# 787 with and data purge activity is running for deleting a transactional table with 10million records.
Then I need to kill the session because of some reasons. I fired the alter system kill session command for killing the particular session as DBA. Then it show me the status marked as killed.

1. Connected as sysdba and fire the kill session command:

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

2. Check the status of session:

SQL> select username, status from v$session where sid=13;

SID      STATUS
-------- ----------
13       KILLED


3. Now i want to check how much it will take to completed killed my session then need to check v$transaction view for rollback the transaction as below:

SQL> select used_ublk from v$transaction;
USED_UBLK
---------
5298

SQL> select used_ublk from v$transaction;
USED_UBLK
---------
1349

SQL> /
no rows selected

4. Now session is completed killed.

SQL> select username, status from v$session where sid=13;
no rows selected