Kill oracle idle session because of security reason
                                                 Last update (2010-07-04 10:54:10)
                                                                                                                   Date added (2008-06-14 20:01:07)

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

Killing idle sessions for some time is good for:
Security reasons and
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;
Other links
Kill idle E-Business Suite application users
Kill fast oracle sessions in Unix - Linux while shutdown
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search