Fight againts Oracle Idle Sessions I


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;


One response to “Fight againts Oracle Idle Sessions I

  1. i will post some tips about this in future


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

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s