SESSION处于KILLED状态下如何找出对应的进程？

今天在处理一个会话满了的数据库，需要释放部分无用的session。根据V$SESSION的program可以简单确定PL/SQLDEV连过去的会话基本是无效会话，需要把这些会话都kill掉：

SQL> select 'alter system kill session '''||sid||','||SERIAL#||''';' from v$session where program like 'PlSqlDev.exe%';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------------------------------------
alter system kill session '12,14459';
alter system kill session '18,379';
alter system kill session '35,63717';
alter system kill session '43,37798';
alter system kill session '56,38333';
alter system kill session '106,59769';
alter system kill session '138,18817';
alter system kill session '163,64575';
alter system kill session '186,18270';
alter system kill session '219,43397';
alter system kill session '266,32098';
alter system kill session '267,64616';
alter system kill session '306,15258';
alter system kill session '309,21923';

--执行以上语句

--抽查其中一个会话的状态

SQL> select * from v$session where sid=12;

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                          COMMAND
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ----------
   OWNERID TADDR            LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME
---------- ---------------- ---------------- -------- --------- ---------- ------------------------------
OSUSER                         PROCESS      MACHINE
------------------------------ ------------ ----------------------------------------------------------------
TERMINAL                       PROGRAM                                          TYPE       SQL_ADDRESS
------------------------------ ------------------------------------------------ ---------- ----------------
SQL_HASH_VALUE SQL_ID        SQL_CHILD_NUMBER PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID   PREV_CHILD_NUMBER
-------------- ------------- ---------------- ---------------- --------------- ------------- -----------------
PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID
--------------------- ------------------------- --------------- -------------------
MODULE                                           MODULE_HASH ACTION                           ACTION_HASH
------------------------------------------------ ----------- -------------------------------- -----------
CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE#
---------------------------------------------------------------- -------------------- ------------- --------------
ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME   LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI
--------------- ------------- ------------ ------------ --- ------------- ---------- ---
RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
-------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER                                                BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION
---------------------------------------------------------------- ----------- ----------------- ----------------
      SEQ#     EVENT# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- ----------------
P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS#
---------------------------------------------------------------- ---------- ---------------- ------------- -----------
WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- --------------- -------------------
SERVICE_NAME                                                     SQL_TRAC SQL_T SQL_T
---------------------------------------------------------------- -------- ----- -----
00000000FC523C28         12      14459    2217887 00000000FC50C798         40 ID5ZZ                                   0
2147483644                                  KILLED   PSEUDO            40 ID5ZZ
liping                         7380:6428    WORKGROUP\GZT-120
GZT-120                        PlSqlDev.exe                                     USER       00
             0                                00000000D5331888      2674323898 bydf32qgqdwdu                 1

PL/SQL Developer                                  1190136663 SQL Window - select * from query  3567217222
                                                                             11937839         14097              6
            380             0 16-APR-08           20262 NO  NONE          NONE       NO
                                 DISABLED ENABLED  ENABLED                       0
                                                                 NO HOLDER
       106        257 SQL*Net message from client
driver id                                                        1413697536 0000000054435000
#bytes                                                                    1 0000000000000001
                                                                          0 00                  2723168908           6
Idle                                                                      0           20262 WAITING
SYS$USERS                                                        DISABLED FALSE FALSE

可以看到会话的状态为killed，等了十分钟，会话的状态仍然没有改变，该会话仍然在V$SESSION中，也就是仍然占用的连接。此时可以考虑直接在操作系统上直接kill掉对应的进程。

但是由于ORACLE的bug，此时是没有办法直接关联V$SESSION和V$PROCESS得到会话对应的SPID的：

Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS

此时的解决办法是：

SQL> select spid, program from v$process
  2      where program!= 'PSEUDO'
  3      and addr not in (select paddr from v$session)
  4      and addr not in (select paddr from v$bgprocess);

SPID         PROGRAM
------------ ------------------------------------------------
18180       oracletest@myserver
18206       oracletest@myserver
15851       oracletest@myserver
15321       oracletest@myserver
15327       oracletest@myserver
19766       oracletest@myserver
15095       oracletest@myserver
15101       oracletest@myserver
15109       oracletest@myserver
15267       oracletest@myserver
32742       oracletest@myserver
15477       oracletest@myserver
22059       oracletest@myserver
22550       oracletest@myserver

以上的查询出来的SPID就是之前我们删除的会话对应的SPID。