Kill All, Pretty cool

This statement is written by my colleage Yousef Swiess (Database Consultant), it collects all the sessions from the database which are causing enques and the out put of this statement is ready to kill the sessions at OS(UNIX) level.

SQL> select 'kill -9 '||p.spid
from gv$process p, gv$session s, gv$session_wait sw
where (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
and sw.sid in (select sw.sid
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.event not in
('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and seconds_in_wait > 0
and (sw.inst_id = s.inst_id and sw.sid = s.sid)
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
and s.username in ('USERNAME','USERNAME'))
and sw.inst_id=&n
/

write the username in the statement and after running this statement you have to give the parameter value for the instance id (inst_id)

Enjoy the easy administration.