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

当归1024

于 2024-03-07 09:06:30 发布

阅读量554

点赞数 2

分类专栏：数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_40603125/article/details/136523576

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

背景

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

排查问题

找到现在所有的链接

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

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

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 客户端操作系统用户名

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

找进程

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

杀进程

ALTER SYSTEM KILL SESSION '16,31364' IMMEDIATE;

批量杀进程

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