Killing the idle session in apps

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