背景
- 数据库链接的时候,提示链接不上,主要原因是:链接数爆了,链接不上了
排查问题
找到现在所有的链接
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%';