Cause of too many inactive session and clean them in Oracle

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.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.


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

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.