Skip to main content

ORA-00031: session marked for kill




SQL> ALTER SYSTEM KILL SESSION '222,18784' immediate;

 ALTER SYSTEM KILL SESSION '222,18784' immediate
*
ERROR at line 1:

 ORA-00031: session marked for kill

* Here the issue is in what this alter system kill command is doing. 

* Alter System Kill command > It’s not actually killing the target session (like kill -9 would do for OS processes). 

* It just sets a bit in the target sessions state object, which marks that the target session should end. 

* But its entirely with the target session to check this bit and act on it.

* All the kill session command is doing is ASK the target session to clean up and exit – via setting that bit.

* The session trying to issue the kill will hang for 60 seconds and then return this “session marked for kill” message. And the target session does not get killed at all.

* The “ORA-00031: session marked for kill” message you see after 60 seconds just means that:

* Session marked for  “kill” bit in target sessions state object. Session waits the target session to die for 60 seconds and times out after it doesn't happen It returns “session marked for kill” error.

* In this situation we can find out the PID kill and kill it like below

SQL> show user;
USER is "SYS"

SQL> select name from v$database;

NAME
---------------------------
VKRK11

SQL> SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 222;

SPID                                        SID    SERIAL#
------------------------------------ ---------- ----------
14235                                       222      18784

SQL> ! kill -9 14435

SQL> SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 222;

no rows selected

Its killed now .. 

Thank You!


Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more