Cause of too many inactive session and clean them in Oracle
1. Check the inactive session present in database
-- 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 p
WHERE p.addr=s.paddr and s.status='INACTIVE'
order by 1,4;
2. Inactive session is caused due to Dead Connection or IDLE Connection
DEAD Connection is handle by SQLNET.ORA file by configuring parameter SQLNET.EXPIRE_TIME=minutes
Open the SQLNET ORA file and set the parameter. it is client file so set it in client side.
SQLNET.EXPIRE_TIME=60 (1 hour)
IDLE Connection is set at USER PROFILES, define profile with IDLE_TIME limit so that INACTIVE SESSION killed after the time limit reached.
-- 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;
Note: idle_time parameter will disconnect sessions after n minutes of inactivity.
Note: We can also use Resource Manager to kill INACTIVE SESSION that are idle crossed the timelimit of MAX_IDLE_TIME limit.
Following is the link to configure RESOURCE MANAGER for KILL INACTIVE SESSION
Smart way of Technology 