Showing posts with label Status "Killed". Show all posts
Showing posts with label Status "Killed". Show all posts

Saturday, April 2, 2016

Clearing the Sessions With Satus "Killed"

After clearing the session from Database with alter system kill session The status showing killed in v$session. And not getting relesed, Casuing performace issues,
select USERNAME,STATUS,count(1) from v$session 
where STATUS='KILLED' group by USERNAME,STATUS 
order by 1 desc;

USERNAME STATUS COUNT(1)
----------------- -------- ----------
ABC KILLED 158
XYZ KILLED 73
TEST KILLED 21
the above schema sessions status marked for killed,We were unable to locate the spid for the Killed Sessions,

sql>SELECT b.spid from v$session a,v$process 
b where a.paddr=b.addr and a.status ='KILLED';
no rows selected.
The following Query helped us in identifying and clearing the sessions with satsus "Killed"

select spid, program from v$process 
where program!= 'PSEUDO'
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess)
and addr not in (select paddr from v$shared_server); 

Note : How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? (Doc ID 387077.1)
After clearing the sessions from OS level , The systen performance restored back to normal, Thus we were able to aviod the Database bounce.
~~ Cheers