oracle 数据链接过多，导致后续链接链接不上

背景

• 数据库链接的时候，提示链接不上，主要原因是：链接数爆了，链接不上了

排查问题

找到现在所有的链接

SELECT
    sysdate AS "当前时间",
    machine AS "客户端主机名",
    program AS "客户端程序",
    username AS "用户名",
    osuser AS "客户端操作系统用户名",
    sid AS "会话ID",
    serial# AS "序列号",
    status AS "会话状态",
    server AS "服务器模式",
    logon_time AS "登录时间"
FROM
    v$session
1
2
3
4
5
6
7
8
9
10
11
12
13

也可以根据 查询所有用户的对应的链接数

SELECT 客户端操作系统用户名,
COUNT(客户端操作系统用户名)
FROM
(SELECT
    sysdate AS "当前时间",
    machine AS "客户端主机名",
    program AS "客户端程序",
    username AS "用户名",
    osuser AS "客户端操作系统用户名",
    sid AS "会话ID",
    serial# AS "序列号",
    status AS "会话状态",
    server AS "服务器模式",
    logon_time AS "登录时间"
FROM
    v$session) T
		GROUP BY 客户端操作系统用户名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

找到用户最多的人的所有的进程，然后杀死

找进程

SELECT
    sysdate AS "当前时间",
    machine AS "客户端主机名",
    program AS "客户端程序",
    username AS "用户名",
    osuser AS "客户端操作系统用户名",
    sid AS "会话ID",
    serial# AS "序列号",
    status AS "会话状态",
    server AS "服务器模式",
    logon_time AS "登录时间"
FROM
    v$session
WHERE machine like '%RCD1224%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

杀进程

ALTER SYSTEM KILL SESSION '16,31364' IMMEDIATE;
1

批量杀进程

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS "KILL SESSION COMMAND"
FROM v$session
WHERE machine like '%RCD1224%';
1
2
3