keeping eye on sessions

from 11g onwards you can keep eye on long running queries(queries taking more than 5 seconds) from sql monitoring facility. You can get same by setting hint MONITOR in any query no matter it executes within 5 seconds


Old method:

--Long operations:

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,
time_remaining remaning,elapsed_seconds elapsed,last_update_time last_time
from v&session_longops --where sid=73
order by last_update_time desc;

--All active sessions
select * from v&session where status='ACTIVE'
--and sid in (37,43)
order by sid;

--Find session's sid or process id by it's sid or process idselect sid, a.serial#, spid, a.username, status, taddr, a.program
from v&session a, v&process b
where a.paddr=b.addr and a.username is not null
--and (sid=163 or spid=28179)
order by status, sid;

--Kill sessionalter system kill session '&sid,&serial';