Session Related Scripts

How to kill inactive sessions logged in more than 15 minutes.

Many a times, we get request from users to kill the sessions which are recently logged in their database. You can use below query to check sessions logged in more than 15Minutes

SELECT 'alter system kill session (' || SID || ',' || SERIAL# || ') immediate;'FROM V$SESSION WHERE (USERNAME = 'WATSON' AND STATUS = 'INACTIVE' and seconds_in_wait > 900);

'ALTER SYSTEM KILL SESSION('||SID||','||SERIAL#||');'                              
--------------------------------------------------------------------------------
alter system kill session (531,2378) immediate;                                              
alter system kill session (613,3053) immediate;                                              
alter system kill session (762,351) immediate;                                              
alter system kill session (843,5830) immediate;                                              
alter system kill session (126,116) immediate;                                              
alter system kill session (456,1333) immediate;           

Below are some more scripts related to Sessions

To Check the session details

=========================================

select sid,serial#,to_char(logon_time,'DD-MON-YY HH24:MMS') "StartTime",status,action

action from  v$session where SID = &&SID;

Wait Event for specific  SID :

=================

select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;

Inactive Sessions Details

====================

set lines 200 pages 2000

col program for a30

col machine for a23

col username for a10

col osuser for a22

col schemaname for a10

col LOGON_TIME for a22

select sid,serial#,machine,program,osuser,username,schemaname,status,to_char(logon_time, 'dd-mon-yyyy hh24:mi:ss') as "logon_time" from v$session

where username is not null and username not in ('SYS','SYSTEM') order by logon_time;

Below Query spool inactive session details to kill:

=========================================

set head off

spool kill_sql.sql

select 'alter system kill session '||''''||SID||','||serial#||''''||' immediate;' from v$session where sid IN

(select SID from v$session where action like 'FRM:%' and status = 'INACTIVE' and logon_time > sysdate -1 );

spool off

=========================================

To find details of frond end users using SID

=============================================================

SELECT b.SID SID, c.spid,b.serial# "Serial#", d.user_name "User Name", logon_time,

ROUND ((SYSDATE - logon_time) * 24, 2) "session_time(HR)", status

FROM fnd_logins a, v$session b, v$process c, fnd_user d

WHERE b.paddr = c.addr

AND a.pid = c.pid

AND a.spid = b.process

AND d.user_id = a.user_id

AND (d.user_name = 'USER_NAME' OR 1 = 1)

AND ((SYSDATE - logon_time) * 24) > 10

AND b.SID=&Session_id

ORDER BY d.user_name, logon_time;

======================================

Locked Session Details:

======================================

col "logon" for a16

col username for a12

col spid for 99999

col osuser for a10

col machine for a15

col sid for 999999

col program for a25

col module for a20

select a.username,a.sid,a.serial#,a.process ,b.spid,

to_char(a.logon_time,'dd:mm:yyyy hh24:mi')

"logon", a.status,a.osuser,a.machine,a.program,a.module from v$session a, v$process b

where a.paddr=b.addr and a.username is not null and a.sid=&sid

order by logon_time;

=========================================