Many times it happens that a session is opened for a user, used for some time and then it is left idle for hours and sometimes for even days. These idle sessions might result in security threats or cause so much overhead on the server. It is therefore important to kill idle sessions for security reasons, performance issues and to free up system resources. In this article we will discuss some effective tips for killing the idle sessions.

Making Idle Session SNIPED:

An idle session can be setup to become sniped after x minutes by setting the initialization parameter resource_limit = true in the init.ora and idle_time in the user profile. You can make user session becomes sniped after 8 hours of idle time by running below command:

alter profile DEFAULT set idle_time=480;

Finding the SNIPED Sessions:

Below query can be used to get the SNIPED idle sessions and kill them.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, SID, v$session.SERIAL#, v$process.SPID UNIX_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, 'alter system kill session ' || '''' || SID || ', ' || v$session.serial# || '''' || ' immediate;' kill_sql FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;

Killing Oracle Idle Sessions While Shutdown (UNIX - LINUX):

Whenever we shutdown our database with IMMEDIATE then we have to wait till all processes gets terminated. More the database has open sessions, more the time it will likely take to terminate them. Below command can be executed in UNIX shell to kill all Oracle sessions where database SID is OTE. It does not kill the SMON and PMON processes, only the LOCAL=NO.

$ ps -ef|grep 'oracleOTE (LOCAL=NO)'|grep -v grep|awk '{print$2}'|xargs -i kill {}

Kill Idle Sessions for E-Business Suite Application Users:

It sometimes happen that user gets login to E-Business Suite, do some work and then leave the forms open staying idle for a long time. Below code can be used to manually kill all form idle sessions that are idle for 3 hours.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process unix_appl, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE, 'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE (v$session.paddr = v$process.addr) AND (v$session.status = 'INACTIVE') AND (v$session.username = 'APPS') AND (v$session.last_call_et/3600 > 3) AND (v$session.action LIKE 'FRM%') --AND v$session.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
ORDER BY logon_time ASC; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle: Data Dictionary for Database Metadata
   7 ways of SQL injections and 7 guidelines to avoid them
   The Secrets of Single-Set Aggregates for DML Statements!!
   Great Tips for Test Data Generation in Oracle!!