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

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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.