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

 

Advertisements

One thought on “ORA-00031: session marked for kill in Oracle

  1. Pingback: Check the blocking or deadlock in Oracle database | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.