Oracle-失效链接清理

1.查看失效的连接数
SELECT count(*) FROM gv$session WHERE inst_id = 1 and status = 'INACTIVE'

2.查看总连接
SELECT count(*) FROM gv$session WHERE inst_id = 1

3.查看失效链接
SELECT SID, SERIAL#,
    MODULE, STATUS
    FROM V$SESSION S
    WHERE S.USERNAME IS NOT NULL
    AND S.LAST_CALL_ET >= 60*60*2
    AND S.STATUS = 'INACTIVE'
ORDER BY SID DESC;

4.RAC模式下查看超过18小时的失效链接
SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
    FROM gv$session S
    WHERE S.USERNAME IS NOT NULL
    AND S.LAST_CALL_ET >= 18*60*60
    AND S.STATUS = 'INACTIVE'
 ORDER BY INST_ID DESC

5.查看失效链接的sql语句
    SELECT s.SID, s.SERIAL#,
    s.INST_ID, s.MODULE,s.STATUS,y.sql_text
    FROM gv$session S,v$sql y
    WHERE S.USERNAME IS NOT NULL
    AND S.LAST_CALL_ET >= 18 * 60*60
    AND S.STATUS = 'INACTIVE' AND s.sql_address = y.address
    ORDER BY INST_ID DESC

6.杀掉失效链接
ALTER SYSTEM KILL SESSION 's.SID, s.SERIAL#';--有回滚
alter system disconnect session 's.SID, s.SERIAL#';--更安全

7.查看失效链接的使用用户以及使用程序
然后可以登录使用程序的机器 kill -9 &spid(即查询出来的进程号spid)

select spid, osuser, s.program
from v$session s, v$process p,(     SELECT SID
    FROM v$session S
    WHERE S.USERNAME IS NOT NULL
    AND S.LAST_CALL_ET >= 18 * 60*60
    AND S.STATUS = 'INACTIVE')c
where s.paddr = p.addr and s.sid =c.sid;

然后登录使用程序的机器 kill -9 &spid(即查询出来的进程号spid)

8.批量杀的sql拼接
SELECT 'alter system kill session ''' || s.sid || '' || ',' || s.serial# ||''';'
    FROM gv$session S
    WHERE S.USERNAME IS NOT NULL
    AND S.LAST_CALL_ET >= 18 * 60*60
    AND S.STATUS = 'INACTIVE'