10 Replies Latest reply on Jun 12, 2008 10:06 AM by 119642

    Session marked for kill

      I need to kill a blocking session. but when i try to do so, it raises an ORA-00031: session marked to kill
      alter session kill .. does not work.
      how can i get rid of that session?
        • 1. Re: Session marked for kill

          ORA-00031:     session marked for kill
          Cause:     The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
          Action:     No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

          So according to the error description,you need to wait for some time.It should go away.
          • 2. Re: Session marked for kill
            You did not provide your OS but the best way to kill a session is to find the process id of the session and kill it from the operating system. After the os process is killed PMON cleans up the session.

            Especially in highly concurrent systems I have not been able to kill a session using 'alter system kill session'. It marks the session to be killed, just like your case does, and keeps it for several hours in that situation.

            Killing the os process always worked for me.
            • 3. Re: Session marked for kill

              Ya thats right, before killing any sql session, get the spid (pid) of that sid and after killing the session if it show that "Session marked to kill" then you can kill the process id at os prompt.

              • 4. Re: Session marked for kill
                i am waiting for 4 hours!
                but it does not go away..

                i am on unix .. how can i find the os level process id?
                i dont want to accidentally kill pmon or any other process :)
                how can i be sure? any help would be appreciated.
                • 5. Re: Session marked for kill
                  Check out V$process view.It has PROCESS column which is the o/s process for the session connected.You can kill that session.
                  • 6. Re: Session marked for kill
                    As you are on Unix killing the Operating system process will cause process recovery to cleanup the database process quicker.
                    • 7. Re: Session marked for kill

                      thats what resides in my v$session is :     5364:5792     
                      but i cant find any processes like 5364 or 5792 on os?
                      ps -ef|grep 5364 and 5792 returns nothing!
                      • 8. Re: Session marked for kill
                        Look at v$process and spid run the sql below:-

                        set linesize 120
                        col sid for 999
                        col username for a14 trunc
                        col osuser for a18 trunc
                        col spid for 99990
                        col logon_time for a12
                        col status for a9 trunc
                        col machine for a26 trunc
                        col running for a10 trunc
                        select s.sid
                        , s.username
                        , s.osuser
                        , s.machine
                        , s.status
                        , p.spid spid
                        , to_char( logon_time, 'Mon dd@hh24:mi') logon_time
                        , rtrim (s.module)||decode( nvl(length( rtrim(s.module)),0),0,'',' ')|| upper(s.program) running
                        from v$session s
                        , v$process p
                        where ( p.addr = s.paddr ) and s.type!='BACKGROUND'
                        and upper(s.program) not like '%CJQ0%' and s.program is not null and s.username is not null
                        order by s.sid;
                        • 9. Re: Session marked for kill
                          ok, i got the session id with this query!!!

                          thank you so much!!
                          • 10. Re: Session marked for kill
                            Glad to help mate