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-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

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more