SQL Query for displaying locks

[anandkl]

Hi friends,
Can u please give me a sql query which displays sessions holding the locks and sessions that are waiting to acquire a lock and both sessions must be 'ACTIVE'

regards
anandkl

[soniaarora]

The 2 scripts below shows the blockers and waiters of lock

Report Sessions Waiting for Locks


SET ECHO off
REM NAME: TFSLCKWT.SQL
REM USAGE:"@path/tfslckwt"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$SESSION, V$LOCK
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Joe Sparks
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Reports users waiting for locks.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM USERNAME SID TYPE HELD REQ ID1 ID2
REM --------------- ----- ---- ----------- ----------- -------- --------
REM SYSTEM 12 TX Exclusive None 131087 2328
REM SCOTT 7 TX None Exclusive 131087 2328
REM SCOTT 8 TX Exclusive None 131099 2332
REM SYSTEM 10 TX None Exclusive 131099 2332
REM SYSTEM 12 TX None Exclusive 131099 2332
REM
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:

column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
spool off
clear breaks



This script generates a fairly easy to read report of locks being held
(or waiting) in the database.


=============
Requirements:
=============

SELECT privileges on V$LOCK, V$SESSION


=======
Script:
=======

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSULOCK.SQL
REM USAGE:"@path/TFSULOCK"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT ON V$LOCK, V$SESSION
REM :
REM Shows User Lock Information
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Sess Op Sys OBJ NAME or

REM ID USERNAME User ID TERMINAL TRANS_ID TY Lock Mode Req Mode
REM ---- -------- -------- -------- ------------- -- ----------- --------
REM 7 SCOTT usuppor ttyr5 TA TM Row Excl
REM 7 SCOTT usuppor ttyr5 Trans-196623 TX Exclusive
REM 8 SCOTT usupport ttyr1 TABLE_CONFIG TM Row Excl
REM 8 SCOTT usupport ttyr1 Trans-131099 TX Exclusive
REM 10 SYSTEM usupport ttyqe TABLE_CONFIG TM Row Excl
REM 10 SYSTEM usupport ttyqe Trans-131099 TX --Waiting-- Exclusiv
REM 11 SYS usupport ttyr8 GTEMP TM Row Excl
REM 11 SYS usupport ttyr8 Trans-196622 TX Exclusive
REM 12 SYSTEM usupport ttyr6 INDEX_BLOCKSTM Row Excl
REM 12 SYSTEM usupport ttyr6 Trans-131080 TX Exclusive
REM
REM Main text of script follows:

set echo off
set pagesize 60
Column SID FORMAT 999 heading "Sess|ID "
COLUMN OBJECT_NAME FORMAT A17 heading "OBJ NAME or|TRANS_ID" Trunc
COLUMN OSUSER FORMAT A10 heading "Op Sys|User ID"
COLUMN USERNAME FORMAT A8
COLUMN TERMINAL FORMAT A8 trunc
select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
DECODE(B.ID2, 0, A.OBJECT_NAME,
'Trans-'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
and B.SID = C.SID
and C.USERNAME is not null
order by B.SID, B.ID2;

[Tarry]

This one will leftpad the locker

 select lpad('',decode(l.xidusn,0,3,0))||l.oracle_username "User Name",
 o.owner,o.object_name,o.object_type
 from v$locked_object l, dba_objects o
 where l.object_id=o.object_id
 order by o.object_id,1 desc;