SESSION處於KILLED狀態下如何找出對應的程式

今天在處理一個會話滿了的資料庫，需要釋放部分無用的session。根據V$SESSION的program可以簡單確定PL/SQLDEV連過去的會話基本是無效會話，需要把這些會話都kill掉：[@more@]

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 WORKGROUPGZT-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
18206
15851
15321
15327
19766
15095
15101
15109
15267
32742
15477
22059
22550

以上的查詢出來的SPID就是之前我們刪除的會話對應的SPID。