1.检查当前库最大连接数

select PARA_NAME,PARA_VALUE,FILE_VALUE from v$dm_ini where para_name=‘MAX_SESSIONS’;

2.检查数据库当前连接数

select count(*) total_sess from v$sessions;

3.检查空闲连接数

select count(*) idle_sess from v$sessions where state=‘IDLE’;

4.定位超过一天未释放的空闲连接信息（提供给开发）

select count(*) idle_sess from v$sessions where state=‘IDLE’;

5.查看超过一天未释放的空闲连接详细信息

SELECT
sess_id,
user_name,
clnt_ip,
appname,
state,
create_time,
last_recv_time,
ROUND((SYSDATE - last_recv_time) * 86400) idle_second
FROM v$sessions
WHERE
state = ‘IDLE’
AND (SYSDATE - create_time) > 1
ORDER BY idle_second DESC;

6.拼接查杀超过一天的idle会话

SELECT ‘SP_CLOSE_SESSION(’ || sess_id || ‘);’ kill_sql,
sess_id,clnt_ip,create_time,idle_second
FROM (
SELECT
sess_id,
clnt_ip,
create_time,
ROUND((SYSDATE - last_recv_time) * 86400) idle_second
FROM v$sessions
WHERE
state = ‘IDLE’
AND (SYSDATE - create_time) > 1
) t
ORDER BY idle_second DESC;

7.检查当前库连接数

8.通知开发再次连接，反馈成功