Sniped Session
Sniped Session is occurred when IDLE_TIME is set in users or the default profile exceed then this will kill the sessions in the database then status column value in v$session view becomes SNIPED.
The session is not always clean then 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 need to be killed manually which present in the v$session.
Another method to clean the sniped session with set SQLNET.EXPIRE_TIME parameter in sqlnet.ora file
Cause:
Set the idle_time parameter in the user profile(seconds)
alter profile senior_claim_analyst limit idle_time 900;
Solution
1. Check the Sniped session present
select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;
OR
select a.sid,a.serial#,b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.status= 'SNIPED';
Note:
ACTIVE – Session currently executing SQL
INACTIVE – Session is inactive
KILLED – Session marked to be killed
CACHED – Session temporarily cached for use by Oracle*XA
SNIPED – Session inactive, waiting on the client
2. Killed the Snipped session
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='SNIPED' ;
3. For avoid snipped session in Oracle
Set the EXPIRE_TIME value in SQLNET.ORA file.