One might face a situation where he wants to know what all queries are running on an Oracle DB or may be there is a lock on a table due to a update/delete query and you want to kill that query. In such cases, you can use following query to display all the sessions, the related queries, status, etc.

column sid          format 999999           heading ‘SID’
column sh           format 999999           heading ‘Serial#’
column pid          format a18              heading ‘PID’
column ia           format a1               heading ‘Active’
column iw           format a1               heading ‘Waiting’
column secs         format 999999999        heading ‘Seconds|In Event’
column event        format a25              heading ‘Event’
column sql          format a45              heading ‘SQL’
set linesize 132
set pagesize 50

select  s.sid sid,
s.serial# sh,
s.process pid,
decode (s.status, ‘INACTIVE’, ‘N’, ‘Y’) ia,
decode (sw.wait_time, 0, ‘Y’, ‘N’) iw,
decode (sw.wait_time, 0, sw.seconds_in_wait, sw.seconds_in_wait-sw.wait_time/100) secs,
sw.event event,
decode (sq.sql_text, null, ‘ – Not Available -’, sq.sql_text) sql
from    v$session_wait sw,
v$session s,
v$sql sq
where   s.sid = sw.sid and
s.sql_hash_value = sq.hash_value(+) and
s.program like ‘%&program%’
and upper(sq.sql_text) not like ‘%CONFIGURATION%’;

And to kill a particular session, use following query:

alter system kill session ‘<Serial#>,<PID>’;

Hope this helps.