Kill oracle idle session because of security reason

Kill oracle idle session because of security reason

Many times users open a session to the database, use it for some time, and then stay idle for hours, even days. They open the form, but they leave it idle for days, until they come back to the office and use the application.

Sometimes users in E-business suite login to E-Business Suite, do some work and just leave the forms open staying idle for a long time. If Oracle Apps client is not doing any activity for some time session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session( both forms & self service) after idle time value is reached to the one mentioned in configuration file.

From FND.G or 11.5.9 or with introduction of AppsLocalLogin.jsp to enter into application, profile option "ICX Session

Timeout" is used only to determine Forms Session Idle timeout value . This might be confusing as earlier this profile option used to control forms as well as self service application(with session.timeout) session.timeout is used to control Idle session timeout for Self Service Applications (Served by Jserv via JVM).

Killing idle sessions for sometime is good for:

a) Security reasons and

b) Releasing resources to the server, especially memory.

The best and most effective technique in "killing the idle sessions" is making the session SNIPED

How to make an idle session get SNIPED

You must set:

A.The initialization parameter resource_limit = TRUE in the init.ora

alter system set resource_limit=TRUE scope=both;

B.Idle_time in the user profile

then you setup idle sessions to become sniped after x minutes.

With the following example the user session becomes sniped after 8 hours of idle time.

alter profile DEFAULT set idle_time=480;

Finding the SNIPED sessions and killing them.

Use the following query to get the sniped idle sessions.

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;