关于session 在killed状态下的疑问？

swgsw

oracle: 10.2.0.4
OS:OEL5.5

alter system kill session(106,10172);
alter system kill session(105,3633);

select ses.username,
       ses.OSUSER,
       ses.sid,
       pro.spid,
       ses.serial#,
       ses.lockwait,
       ses.status,
       ses.machine,
       ses.program,
       'kill -9 ' || pro.spid as "OPSYS_Command"
  from v$session ses
  left join v$process pro
    on pro.addr = ses.paddr
where ses.status = 'KILLED'
   and ses.OSUSER = 'tl'
/*and pro.spid is not null*/
order by ses.machine;


USERNAME   OSUSER   SID SPID      SERIAL# LOCKWAIT   STATUS   MACHINE       PROGRAM        OPSYS_Command
---------- ------------ ------ ---------- ---------- -------- ------------- -------------- -------------
CALLCENTER tl       106             10172            KILLED   YC\ENCH-TL    plsqldev.exe   kill -9
CALLCENTER tl       105              3633            KILLED   YC\ENCH-TL    plsqldev.exe   kill -9


问题1：请问当一个session 用alter system kill session这句话kill以后，数据库都做了些什么？

问题2：上面的查询看不到SPID？

Maclean Liu(刘相兵

ODM FINDING:



ALTER SYSTEM KILL SESSION
-------------------------

This document describes the behavior of the ALTER SYSTEM KILL SESSION
command new in version 7 of the RDBMS.  This command allows the DBA to
terminate a user's Oracle session as rapidly as possible by interrupting
any work it is doing and freeing all of it's resources.  This may be useful
when the session has been holding resources that other users need.

I.  SYNTAX:

   ALTER SYSTEM KILL SESSION 'i,j';

     where:

       i = the index of the session to kill
       j = the serial number of the session to kill

    Both of these values can be found in the v$session view which lists
  them for each session currently logged on to the database.  Both i & j
  are required in the command to uniquely specify the session to be deleted.
  The session address (SADDR column of v$session) is insufficient for this
  purpose because it may be reused at any time for newly allocated sessions.


II.  USER DOCUMENTATION

    To use the ALTER SYSTEM KILL SESSION command, it is necessary to be
  logged on as a user with DBA privileges.  Then, the index and serial number
  of the session which is to be killed must be queried from the database.  
  There are two different behaviors that may be exhibited by the KILL SESSION
  command and they depend on whether the session to be killed is currently
  IDLE or ACTIVE.


  A.  Killing Idle (Inactive) Sessions

      As an example, suppose that user SCOTT is logged on to the database
    and a DBA executes the following query:

    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             7         11 SCOTT                          INACTIVE DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    7 rows selected.
    SQLDBA>

      From this result, we can see that SCOTT's session has an index number
    of 7 and a serial number of 11.  His session is currently inactive, as
    indicated by the STATUS column; this means he is not currently calling
    the database to execute SQL.

      Now, let's issue the kill command and see what happens --

    SQLDBA> alter system kill session '7,11';
    Statement processed.
    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             7         11 SCOTT                          KILLED   PSEUDO
             9         11 SYS                            ACTIVE   DEDICATED
    7 rows selected.
    SQLDBA>

      Note that the STATUS of SCOTT's session is now "KILLED".  This means
    that his session has been marked killed by Oracle and he can no longer
    initiate database calls.  It also means that all the resources he may
    have had locked (table locks, tx locks, etc) have been freed.
  
      Also note that the SERVER column for SCOTT's session is now "PSEUDO".
    This means that his session is no longer being served by his server
    process anymore.  His session is now being served by the 'pseudo-process'
    which is a dummy process Oracle maintains to store dead or detached
    sessions.  The pseudo-process does NOT have an associated operating
    system process -- it is merely an object stored in shared memory which
    is used by Oracle to cleanup sessions.

      So, we've succeeded in killing SCOTT's session, but it still appears in
    the v$session table.  This is because his session state object has not
    yet been deleted (though all it's associated resources have).  The reason
    for this is because SCOTT's process is still alive and thinks it still has
    a session associated with it.  So, when SCOTT again tries to do some SQL
    his process needs to look at it's session, notice that it's been killed,
    and inform SCOTT of this fact.

      From SCOTT's point of view, this is what he sees --

    SQLDBA> connect scott/tiger
    Connected.
    SQLDBA>

      At this point in time, we killed him ... now, he returns and tries to
    perform a query:

    SQLDBA> select * from emp;
    select * from emp
                  *
    ORA-00028: your session has been killed
    SQLDBA>
    SQLDBA> select * from emp;
    ORA-01012: not logged on
    SQLDBA>

      And he gets an ORA-28 saying his session has been killed.  Any further
    attempts at querying the database return ORA-1012 saying he is not logged
    on.  SCOTT can of course login again and he will be allocated a new
    session and he will be able to work as usual.

      Now the DBA looks again at v$session and sees SCOTT's session is truly
    gone:

    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    6 rows selected.
    SQLDBA>


  B.  Killing Active Sessions

      When killing an active session, the behavior is more straightforward.
    Example --

    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             7         11 SCOTT                          ACTIVE   DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    7 rows selected.
    SQLDBA>
    SQLDBA> alter system kill session '7,11';
    Statement processed.
    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    6 rows selected.
    SQLDBA>

      Here, we can see that the session was killed immediately and deleted
    immediately.  This is the case because SCOTT was interrupted by an ORA-28
    error and, realizing that it's session was killed, SCOTT's process deleted
    it's own session state object.  The ALTER SYSTEM KILL SESSION command will
    wait (up to 60 seconds -- see Section C below) until the session has been
    deleted before returning.

      From SCOTT's point of view,

    SQLDBA> connect scott/tiger
    Connected.
    SQLDBA> select * from emp;
    select * from emp
                  *
    ORA-00028: your session has been killed
    SQLDBA> select * from emp;
    ORA-01012: not logged on
    SQLDBA>


  C.  Marked for Kill

      It is sometimes possible that the session to be killed is busy doing
    some activity that cannot be interrupted (network i/o, rollback, etc).
    In this case, if the uninterruptable activity lasts for more than a
    minute, ALTER SYSTEM KILL SESSION will return ORA-31 saying that the
    session has been marked killed, but it won't be deleted until the
    uninterruptable activity is finished.  Thus, the session's resources
    will not be freed until then also.

      The ALTER SYSTEM KILL SESSION command may take up to 60 seconds to
    return if it is waiting for the session to finish some activity.
    Example --

    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             7         11 SCOTT                          ACTIVE   DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    7 rows selected.
    SQLDBA>
    SQLDBA> alter system kill session '7,11';
    ORA-00031: session marked for kill
    SQLDBA> select sid,serial#,username,status,server from v$session;
    SID        SERIAL#    USERNAME                       STATUS   SERVER
    ---------- ---------- ------------------------------ -------- ---------
             1          1                                ACTIVE   DEDICATED
             2          1                                ACTIVE   DEDICATED
             3          1                                ACTIVE   DEDICATED
             4          1                                ACTIVE   DEDICATED
             5          1                                ACTIVE   DEDICATED
             7         11 SCOTT                          KILLED   DEDICATED
             9         11 SYS                            ACTIVE   DEDICATED
    7 rows selected.
    SQLDBA>

      Note that SCOTT's session is marked "KILLED" above, but it's server
    is still "DEDICATED".  This indicates that as soon as his session's
    current activity is finished, it will terminate itself.


III.  TECHNICAL DOCUMENTATION

A. IMPLEMENTATION

  The ALTER SYSTEM KILL SESSION command is implemented almost entirely by
ksukil() in ksu.c.  Here is the documentation for that function --

void     ksukil(/*_ text *idstr _*/);
/*
   ksukil - Kernel Service User KILl user session                           

       idstr = text string of the form "index,serial"
           index  - the session's index number
           serial - the session's serial number  

   Kills the specified user session by interrupting it's server (if any) and
   forces the session state object to get deleted.  

During the kill, the session state object undergoes the following state
changes --
  
  1) Marked for kill (KSUSFKIL) -- as soon as we are committed to kill this
     session, we set this bit saying it is marked for kill.  This means that
     any further calls to ksupuc() by a server of this session will return
     OER-28 saying this session was killed.  Thus, sessions in this state
     cannot initiate new calls.

  The remaining state changes depend on whether the session is active in a
  call or not.  If the session *is* active in a call, the following occurs:

  2) The killer signals an interrupt in the server of the session and waits
     for the session to complete its call.  In the normal case, this will be
     quick since the call should terminate with the interrupted error.  Then,
     the server will notice that it was interrupted and perform a normal
     "logoff" deleting the session.  

  OR, if the session is *not* in a call (idle):

  2) The killer marks the session for "idle kill".  This sets the KSUSFIKL
     bit saying that no one can delete this session now except for PMON.
     The killer then moves the session state object to the pseudo-process so
     PMON will notice it there and delete all the children of the session.
     The session object itself is not yet deleted, though, until the server
     has cleaned up PGA pointers to it.

  3) Once the server of the session becomes active again (by calling ksupuc),
     it sees that the session was marked for kill and cleans up its PGA
     pointers to that session (see ksuded).  When it has finished cleaning
     up these pointers, it sets the KSUSFIKD bit in the session.  This
     indicates that the session state object can now be deleted.

  4) Finally, PMON sees that the KSUSFIKD bit is set in the session (still
     hanging off the pseudo process) and deletes the session object itself.

  It is possible that the session to be killed is currently blocked in a
  network call or in some other uninterruptable state -- if it remains that
  way for more than 60 seconds, ksukil will return OER-31 to the killer.
  When this happens, the session will kill itself when it finally comes out
  of its uninterruptable call.
*/

  Other functions involved in the kill --

  opiodr() - checks for the OER-28 in it's ONERROR frame and if it
             finds that error, deletes the session by simulating a logoff.  

  ksuded() - now used for both the idle sniper (resource limits) as well as
             kill session; cleans up PGA pointers to session and sets bit
             indicating to PMON that the session can be deleted.

  ksupuc() - check if marked for kill and call ksuded if so.

  ksuxdp() - look for killed sessions on the pseudo-process and delete them
             and/or their children as required.

B. DEBUGGING

If you are having issues with killing sessions it is recommended that you
set the following events:

# ksu debugging:
# (bit 1): Enable ksdwrf tracing of the kill session code path. There is
#          some tracing for both the killer and the killed session.
# (bit 2): Assert if interrupt queue full. ORA-28 signals may get dropped
#          because the target process's interrupt queue is full.
event="10422 trace name context forever, level 3" # ksu debugging

# Every time an ORA-28 is cleared by the killed process this event will force
# a stack dump.
event="10423 trace name context forever, level 28" # trace clears of ORA-28