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

No comments :

Post a Comment