SQL Query for displaying locks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Query for displaying locks

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  2. #2
    Join Date
    Jul 2002
    Posts
    132
    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;


  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    This one will leftpad the locker
    Code:
     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;
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width