Oracle session waiting , enque event , locked session , session killing

1. 
-- Title 'ENUQUE wait events sessions more than 30 minutes '

SELECT vsw.event, vsw.seconds_in_wait "Wait(Secs)", vs.sid, vsa.sql_text
FROM v$session vs,
v$session_wait vsw, 
v$sqlarea vsa
WHERE vs.sid = vsw.sid
AND vs.sql_hash_value = vsa.hash_value
AND lower(vsw.event) LIKE '%enqueue%'
AND vsw.seconds_in_wait > 1800
/

2. 
-- Title 'OTHER wait events sessions more than 30 minutes '
SELECT vs.username,vsw.event, vs.logon_time, vsw.seconds_in_wait "Wait(Secs)", vs.sid,o.object_name,vl.block,vl.type
FROM v$session_wait vsw,
v$session vs,
v$locked_object vlo,
dba_objects o,
v$lock vl
WHERE vsw.seconds_in_wait > 1800
AND vl.block <> 0
AND vs.sid = vsw.sid
AND vlo.session_id = vsw.sid
AND o.object_id = vlo.object_id
AND vl.sid = vsw.sid
/

3.-- Which session is blocking what session. 
-- 
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

4. To Kill a session:

SQL> select USERNAME,SID,SERIAL#,STATUS from v$session
 where USERNAME='USERNAME'; 

-- user name will start from OPS$ if configured in Unix system


USERNAME      SID       SERIAL#            STATUS
------------------------------ ---------- -           --------- --------
USERNAME     66            7730              INACTIVE


SQL> alter system kill session '66,7730';


5. Session information and lockwait:


SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr

ORDER BY s.username, s.osuser;