When idle_time exceeds, the session status becomes 'sniped' in v$session, but sniped sessions never get cleaned up.
Using 'alter system kill session' to kill the session, session status becomes 'killed' in v$session, but still is never cleaned up.

What happens is this:
1) The user leaves their session idle
2) Oracle sets the status to "sniped" (i.e. session is inactive, waiting on client)
3) Once the message is displayed, the session is terminated and the user must reconnect
It does not disconnect the session. It changes the status to from INACTIVE to SNIPED.

Set Parameter
Show parameter resource_limit
SQL> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

If this parameter is false.I changed RESOURCE_LIMIT=TRUE make it true as following
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH

Create New Profile
CREATE PROFILE "INACT_SESS" LIMIT IDLE_TIME 10;
Newly created profile assign to user
ALTER USER apps PROFILE inact_sess;
Query against Sniped status in v$session
Select * from v$session where status=’SNIPED’;

Shell Script to kill sniped processes from OS level :
Suppose Script Name : snip_kill_proc.sh
#!/bin/sh
tmpfile=/tmp/tmp.$$
su - testora -c /d01/oracle/product/10.2.0/bin/sqlplus /nolog < connect / as sysdba
spool kill_sniped_sessions.lst
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
nd s.status='SNIPED';
spool off
EOF
for x in `cat /d01/testora/kill_sniped_sessions.lst | grep "^[0123456789]"`
do
kill -9 $x
done
rm /d01/testora/kill_sniped_sessions.lst
Execute it by issue this command: sh snip_kill_proc.sh

We can Schedule this shell script in crontab as follow :

*/30 * * * * /d01/backup/scripts/snip_kill_proc.sh > /d01/backup/scripts/snip_kill_proc.log 2>&1

For Your Reference : Metalink Note id: 96170.1 Script for killing sniped sessions shadow processes

posted by Jaswinder Singh @ 4:53 AM

0 Comments: