13 Replies Latest reply on Sep 7, 2016 9:35 AM by Naresh Kumar

    killed session exist in V$sessions but not exist in V$process

    Sergey Klimov

      Hi,

       

      I see the killed  sessions in

      SQL> select SID from v$session where username='MyUser' and status='KILLED';

             SID

      ----------

            1580

            1599

        3 rows selected.

       

       

      But I can't to find spid of those sessions to kill them from OS:

       

      SQL> select USERNAME from v$process where username='MyUser';

      no rows selected

       

      or,

       

      SELECT --s.inst_id,

             s.sid,

             s.serial#,

             p.spid,

             s.username,

             s.program

      FROM   gv$session s

             JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id and s.username='MyUser'

      WHERE  s.type != 'BACKGROUND';

      no rows selected

       

      this query give the empty space under the PID for MyUser:

       

      COLUMN sid              format 9999     heading "SID"

      COLUMN spid             format 9999     heading "PID"

      COLUMN username         format a30      heading "User Name"

      COLUMN osuser           format a30      heading "OS User"

      SET FEEDBACK OFF VERIFY OFF

      SET lines 132 pages 59

      TTITLE left _date center 'Oracle System Users' skip 2

      SELECT a.sid, b.spid, a.username, a.osuser

        FROM v$session a, v$process b

      WHERE a.paddr = b.addr(+)

      /

       

      How can I to clear those session from database?

        • 1. Re: killed session exist in V$sessions but not exist in V$process
          AndrewSayer

          Are they actually causing a problem?

           

          Seems similar to How to remove KILLED session

          • 2. Re: killed session exist in V$sessions but not exist in V$process
            Martyn Figueiredo

            Sergey

             

            If I understand correctly your question, you can do it:

             

            SET LINESIZE 200
            COLUMN spid FORMAT A10
            column osuser format a20
            COLUMN username FORMAT A10
            COLUMN program FORMAT A45
            
            
            SELECT s.inst_id,
                   s.sid,
                   s.serial#,
                   p.spid,
                   s.username,
                   s.status,
                   s.osuser,
                   s.program
            FROM   gv$session s
                   JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
            WHERE  s.type != 'BACKGROUND';
            

            With the commands above you can see your killed sessions and the process spid.

             

            So you can kill the process via OS.

             

            Linux:

            kill -9 spid
            

             

            Windows:

            C:\> orakill ORACLE_SID spid
            

             

            Allright?

             

            I hope I've helped you. o/

            • 3. Re: killed session exist in V$sessions but not exist in V$process
              BPeaslandDBA

              If I understand correctly your question, you can do it:

               

               

               

              I do not think you understood at all.

              Your solution is to join to V$PROCESS and as the OP stated, there are no corresponding processes for the sessions...

               

              Cheers,
              Brian

              • 4. Re: killed session exist in V$sessions but not exist in V$process
                BPeaslandDBA

                Are those sessions still in V$SESSION now? What you may be seeing are sessions that need to be rolled back. As you stated, they are marked for kill. PMON needs to clean up the session. The process no longer exists, which is ok. But PMON needs to complete the cleanup. This can take a long time if the session was in the  middle of a long transaction when the session was killed or the process terminated.

                 

                HTH,

                Brian

                • 5. Re: killed session exist in V$sessions but not exist in V$process

                  But I can't to find spid of those sessions to kill them from OS:

                  They are already dead - you can't kill them if they are dead.

                   

                  Besides you do NOT kill Oracle sessions from the OS.

                   

                  How can I to clear those session from database?

                  Why? What PROBLEM are you trying to solve?

                   

                  You don't need to clear them from the database - they are dead. Let the dead rest in peace. If you must mourn for those dead sessions send flowers on their behalf to the local children's hospital.

                  • 6. Re: killed session exist in V$sessions but not exist in V$process
                    Sergey Klimov

                    It is just interesting that exist the sessions without OS processes:

                      SID PID          User Name                      OS User

                    ----- ------------ ------------------------------ ------------------------------

                    1575 17548        SYS                            MyHost

                    1605 18201        SYS                            MyHost

                    1617 18203        SYS                            MyHost

                    1583 18205        SYS                            MyHost

                    1549 20937        FDW                            TKPAdmin

                    1596 18589        DBSNMP                         oraagent

                    1615              MyUser                            MyUser

                    1607              MyUser                            MyUser

                    1600              MyUser                            MyUser

                     

                    And yes, it causing a problem, because we have alert the alert from OEM cloud control.

                    • 8. Re: killed session exist in V$sessions but not exist in V$process
                      AndrewSayer

                      Sergey Klimov wrote:

                       

                      It is just interesting that exist the sessions without OS processes:

                      SID PID User Name OS User

                      ----- ------------ ------------------------------ ------------------------------

                      1575 17548 SYS MyHost

                      1605 18201 SYS MyHost

                      1617 18203 SYS MyHost

                      1583 18205 SYS MyHost

                      1549 20937 FDW TKPAdmin

                      1596 18589 DBSNMP oraagent

                      1615 MyUser MyUser

                      1607 MyUser MyUser

                      1600 MyUser MyUser

                       

                      And yes, it causing a problem, because we have alert the alert from OEM cloud control.

                      Alerts doesn't mean problem.

                       

                      A problem would be these killed sessions holding blocking locks for e.g.

                       

                      Did you check the thread I linked to and the document linked to there? Doc ID 1020720.102 if you must get rid of these sessions then look into that. But again, you have not shown any reason why they are really a problem.

                      • 9. Re: killed session exist in V$sessions but not exist in V$process
                        Sergey Klimov

                        SQL> SELECT inst_id, status, event, state, blocking_session, sid, serial#, program

                             FROM   gv$session

                             WHERE  status = 'KILLED';  2    3

                         

                        there are no blocking sessions for those sessions:

                           INST_ID STATUS   EVENT                                                            STATE               BLOCKING_SESSION        SID    SERIAL#

                        ---------- -------- ---------------------------------------------------------------- ------------------- ---------------- ---------- ----------

                        PROGRAM

                        --------------------------------------------------

                                 1 KILLED   SQL*Net message from client                                      WAITING                                    1502      36983

                        DDTEK ODBC Oracle

                         

                         

                                 1 KILLED   SQL*Net message from client                                      WAITING                                    1504      43895

                        DDTEK ODBC Oracle

                         

                         

                                 1 KILLED   SQL*Net message from client                                      WAITING                                    1511      64881

                        DDTEK ODBC Oracle

                         

                        Alerts doesn't mean problem.

                        I'm agree, but

                        A problem would be these killed sessions holding blocking locks for e.g.
                        sometimes blocking by killed sessions happens, that's why alert was created.

                        But now number of killed sessions is over threshold. In case of new sessions we will not see them.

                         

                        thank you, I will try 1020720.102

                        • 10. Re: killed session exist in V$sessions but not exist in V$process
                          Naresh Kumar

                          Hi ,

                           

                          1. these session are already killed and os process no more exist.

                          2. due to same reason no output from v$process with respact to users process killed.

                          3. After PMON has cleaned up after the session, the row is removed from V$SESSION.

                           

                           

                          Status of the session:

                           

                              ACTIVE - Session currently executing SQL

                           

                              INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits

                           

                              KILLED - Session marked to be killed

                           

                              CACHED - Session temporarily cached for use by Oracle*XA

                           

                              SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.

                           

                          Regards

                          Naresh

                          • 11. Re: killed session exist in V$sessions but not exist in V$process
                            Sergey Klimov

                            seems that my case is

                            Orphan sessions/processes (mismatch between v$session and v$process).

                             

                            SQL> SELECT status,  state, blocking_session, sid, serial#

                            FROM   gv$session      WHERE  status = 'KILLED';   2

                            STATUS   STATE               BLOCKING_SESSION        SID    SERIAL#

                            -------- ------------------- ---------------- ---------- ----------

                            KILLED   WAITING                                    1502      36983

                            KILLED   WAITING                                    1504      43895

                             

                            SQL> select spid,addr from v$process where addr in (select paddr from v$session where sid=1502);

                            no rows selected

                            • 12. Re: killed session exist in V$sessions but not exist in V$process
                              Naresh Kumar

                              OK kill the session by using following command

                               

                              kill session using sid, serial#  with immediate clouse.

                              • 13. Re: killed session exist in V$sessions but not exist in V$process
                                Naresh Kumar

                                these session has been block by session id 1502, 1504.

                                if you kill these session current session also released.