Killed Sniped session in Oracle

 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.

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle