IDLE Session > Sniped Session > Kill > Release Resource

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 1 hours of idle time by running below command:


select * from v$parameter where name like '%resource_limit%';

select * from dba_profiles where resource_name like 'IDLE_TIME';


alter system set resource_limit=true scope=spfile;

alter profile LD_BACKOFFICE limit IDLE_TIME 30;


after 30 minutes status=inactive will become sniped


--------------------Finding Sniped Idle Session--------------------------

SELECT
p.spid "Thread ID",
s.sid "Session ID",
s.serial# "Serial Num",
b.name "Background Process",
s.sql_id "SQL ID",
s.username "Session User",
s.osuser "OS User",
s.status "Status",
s.program "Program"
FROM
v$process p,
v$bgprocess b,
v$session s
WHERE
s.status = 'SNIPED'
AND
s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name IS NULL
ORDER BY s.username ;


-----------------release resource from idle inactive / sniped / killed session-------

orakill SID SPID



-------------script---Sniped.sql--------------------------

store set c:\prevsqlsettings.sql replace

set pagesize 0

set feedback off

set trimspool on

set termout off

set verify off

spool c:\killSniped.bat

select 'orakill ' || i.instance_name || ' ' || spid from v$instance i,v$process p, v$session s,v$bgprocess b where p.addr=s.paddr AND b.paddr(+) = p.addr AND b.name IS NULL and s.status='SNIPED' and s.username != 'SYSTEM' and s.username != 'SYS' and s.username != 'DBSNMP' and s.username != 'REPADMIN' and s.username != 'WMSYS' and s.username != 'TSMSYS' and s.username != 'OUTLN' and s.username != 'ORACLE_OCM' and s.username != 'LDBO' and s.username != 'SNSEXPORT' and s.username != 'RENEXPORT' and s.username != 'CLLVL';

spool off

host c:\killSniped.bat

@c:\sqlsettings

host del c:\killSniped.bat

host del c:\prevsqlsettings.sql

exit


------------Job Schedule-----sniped.bat--------------

@echo
set oracle_sid=sns6
sqlplus ldbo/ldbo@sns1011srv @c:\sniped.sql
exit

--------------------------------------------------------Just for Knowledge-------------------------------------------



Then IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.


SQLNET.EXPIRE_TIME =10


sqlnet.expire_time

sqlnet.expire_time parameter: The database is to detect dead TCP connections, not idle client applications
if you kill an idle session, you don't have a dead connection. A dead connection occurs if you switch off or disconnect your client such that it cannot send a proper FIN to close the TCP/IP connection.


Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client, and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again. A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user (or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates. But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.



------------maual kill session----------------

alter system kill session '526,67';


-------------------------Last activity / work time ----------

select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;


------------find sessions which are running for more than 2 hours----------------

SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC ;


----------------Kill all user sessions except me----------------


1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null

4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;



----------------------------alter kill session --job schedule-------------------

creating a procedure to kill sesssion and schedule it nightly be the best solution?

Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session

'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;

------------------------------