Killed Sniped session in Oracle

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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.