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
    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'