ITPub博客

首页 > 数据库 > Oracle > [20211019]V$DETACHED_SESSION视图.txt

[20211019]V$DETACHED_SESSION视图.txt

原创 Oracle 作者:lfree 时间:2021-10-19 11:21:31 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2838067/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3073
  • 访问量
    6801178