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