[20211019]V$DETACHED_SESSION视图.txt

[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)