Locks Info

----------------------------------
- Database Locks Information
-Gives Locks Details
-Session Information for specified Session ID
-Sql text for specified Session Info
-File Name : locks.sql
-Usage : locks
-----------------------------------

select l1.sid,'is blocking',l2.sid from v$lock l1,v$lock l2
where   l1.id1=l2.id1 and l1.id2=l2.id2 and l1.block=1 and l2.request>0 order by l1.sid
/
set line 200
col program for a16
col MODULE for a16
col action for a35
col 'Time Hrs' for 999999.99
col user@machine  for a35
col (SID,SERIAL#)  for a13
set line 150

select sid||','||serial# "(SID,SERIAL#)",status,last_call_et/3600 "Time Hrs",username||'@'||machine as "user@machine",program,module,action,type 
from gv$session where sid=&SID
/
select s.sql_text
FROM v$process p,v$session a,v$sqltext s,v$sqlarea sa
where 
    a.sid = &SID
and s.address = a.prev_sql_addr
AND p.addr = a.paddr
AND s.address=sa.address
AND a.username is not null
order by a.username, a.osuser, a.sid, s.piece
/