正德厚生,臻于至善

dead connections and inactive sessions

Note:206007.1 – How To Automate Cleanup Of Dead Connections And INACTIVE Sessions
Note:151972.1 – Dead Connection Detection (DCD) Explained
Note:159978.1 – How To Automate Disconnection of Idle Sessions
Note:160386.1 – How to Enable Oracle Net Dead Connection Detection or DCD
Note:395505.1 – How to Check if Dead Connection Detection (DCD) is Enabled in 9i ,10g and 11g
Note:438923.1 – How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any Client/Server Network Tracing
Note:601605.1 – A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
Note:1484302.1 – Master Note Oracle Resource Manager (RM) DBMS_RESOURCE_MANAGER Overview (also go through Section: Applications of RM)
Note:1020004.6 – Script: List Inactive Users
Note:1018160.6 – Common Questions About Dead Connection Detection (DCD)

-- RAC环境用 gv$session ,单机用 V$SESSION
-- 由于kill session是直接将session kill掉,有可能出现导致事物回滚的现象,所以使用disconnect session完成当前事务并终止session。这种方式比alter system kill session跟安全可靠。
set lines 200 pages 50000
select s.inst_id,'alter system kill session '||''''||s.sid||','||s.serial# ||''';'||chr(10)|| '!kill '||p.spid||';' 
from gv$process p, gv$session s 
where s.username is not null 
and s.LAST_CALL_ET > 2*60*60  ----LAST_CALL_ET is NOT the time the session has been INACTIVE but rather the time since the last query was issued
and s.status='INACTIVE' 
and s.type<>'BACKGROUND' 
and s.paddr = p.addr
order by s.inst_id desc; 
set lines 200 pages 50000
select 'alter system kill session '||''''||s.sid||','||s.serial# ||''';'||chr(10)|| '!kill '||p.spid||';' 
from v$process p, v$session s 
where s.username is not null 
and s.LAST_CALL_ET > 2*60*60  ----LAST_CALL_ET is NOT the time the session has been INACTIVE but rather the time since the last query was issued
and s.status='INACTIVE' 
and s.type<>'BACKGROUND' 
and s.paddr = p.addr; 

Also Please go through the previous post for the similar questions,
https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=223093
https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=214483
https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=155240
https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=61984

vi tfsinses.sql
SET ECHO off
REM NAME:   TFSINSES.SQL
REM USAGE:"@path/tfsinses.sql"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on V$SESSION, V$PROCESS, V$SESSION_WAIT
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    This script lists inactive users in the database.  The wait
REM    sequence can be monitored to check whether this really is an
REM    inactive user or not.  The process id's can assist you to
REM    remove the process
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                         Shadow     Parent      Wait
REM    ORACLE/OS User   Term    SID SERIAL# Process ID Process ID  Sequence
REM    ---------------- ------ ---- ------- ---------- ---------- ---------
REM    SYSTEM usupport  ttype     6      21 26351      26350       28
REM
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM    This script is provided for educational purposes only. It is NOT
REM    supported by Oracle World Wide Technical Support.
REM    The script has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:
set heading on feedback on pages 66
column userinfo heading "ORACLE/OS User" format a19
column terminal heading "Term" format a6
column process heading "Parent|Process ID" format a10
column spid heading "Shadow|Process ID" format a10
column seq# heading "Wait|Sequence" format 99999990
select s.username||' '||s.osuser userinfo,s.terminal, s.sid, s.serial#,
p.spid,
s.process , w.seq#
from v$session s, v$process p
,v$session_wait w
where p.addr = s.paddr
and s.sid = w.sid
and w.event = 'SQL*Net message from client'
and s.status = 'INACTIVE'
order by s.osuser, s.terminal
/
赞(0) 打赏
未经允许不得转载:徐万新之路 » dead connections and inactive sessions
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏