[20211019]V$DETACHED_SESSION视图.txt

--//当不小心alter system kill session 'XXX,YYY'时,对应进程并没有从OS清除。再使用原来的方法无法完全清除。
--//一般使用类似语句标识出来。
select spid, program from v$process
    where program!= 'PSEUDO'
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

--//注我的查询这样还是有问题，大家可以测试。

--//11g在v$session 增加了2个字段CREATOR_ADDR,CREATOR_SERIAL#
CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process

--//执行如下：
SELECT spid, program
  FROM v$process
 WHERE addr in  (SELECT creator_addr FROM v$session)
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

--//实际上oracle还提供视图V$DETACHED_SESSION，查询它可能更快解决问题。通过例子说明：

1.环境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       295       1359 36988                    DEDICATED 36989       21        136 alter system kill session '295,1359' immediate;

2.测试：
SYS@book> alter system kill session '295,1359';
System altered.

select spid, program from v$process
    where program!= 'PSEUDO'
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

SPID   PROGRAM
------ ------------------------------
36989  oracle@gxqyydg4 (TNS V1-V3)
57352  oracle@gxqyydg4 (D000)
--//可以发现多了一个D000进程不该杀。改写如下:

SELECT spid, program
  FROM v$process
 WHERE addr in  (SELECT creator_addr FROM v$session)
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

SPID   PROGRAM
------ ------------------------------
36989  oracle@gxqyydg4 (TNS V1-V3)

--//而查询视图V$DETACHED_SESSION也许更快。
SYS@book> select * from V$DETACHED_SESSION;
      INDX PG_NAME                               SID    SERIAL#     PID
---------- ------------------------------ ---------- ---------- -------
         0 DEFAULT                               295       1359      21

--//根据sid，serial#输出，直接执行：
alter system kill session '295,1359' immediate;
--//就可以kill对应进程。

--//也可以执行如下确定SPID进程号。
SELECT spid, program
  FROM v$process
 WHERE addr IN (SELECT creator_addr
                  FROM v$session
                 WHERE (sid, serial#) IN (SELECT sid, serial#
                                            FROM V$DETACHED_SESSION));

SPID   PROGRAM
------ ------------------------------
36989  oracle@gxqyydg4 (TNS V1-V3)