概述
本文讨论如何在oracle中清理非活动的会话
解决方案
1、检查数据库中存在的非活动会话
-- Check inactive and active session count select status, count(1) from v$session group by status; --Check username,programname inactive count select username, program, count(1) from v$session where status='INACTIVE' group by username, program; --Find more details of inactive count select p.username "OSUSERNAME", p.terminal,p.program,s.username "DBUSERNAME",s.command,s.status,s.server,s.process,s.machine,s.port,s.terminal,s.program,s.sid,s.serial#,p.spid FROM v$session s,v$process pWHERE p.addr=s.paddr and s.status='INACTIVE'order by 1,4;
2、Inactive session是由于Dead Connection或IDLE Connection造成的
DEAD 连接由 SQLNET.ORA 文件处理,通过配置参数 SQLNET.EXPIRE_TIME=minutes 打开 SQLNET ORA 文件并设置参数。它是客户端文件,所以在客户端设置它。
SQLNET.EXPIRE_TIME=60 (1 hour)
IDLE 连接设置为 USER PROFILES,定义具有 IDLE_TIME 限制的配置文件,以便在达到时间限制后终止 INACTIVE SESSION。
-- Need to enable resource limit so it automatic terminate the session. alter system set resource_limit=true scope=both; --Check user which profile it uses SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS; --check profile setting for IDLE_TIME select profile, limit from DBA_PROFILES where resource_name = 'IDLE_TIME'; --Alter IDLE TIME to 30 minutes after it disconnect alter profile customers_profiles limit idle_time 30;
注意:idle_time 参数将在 n 分钟不活动后断开会话。
标签:count,username,--,清除,过多非,IDLE,session,TIME,Oracle From: https://www.cnblogs.com/lkj371/p/17024709.html