DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query v$lock

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello DBAs:

    How do I query v$lock for dead lock detection?

    I can look for a trace file and its components by looking at alert_log that we have a deadlock, but otherwise ...

    Thanks, ST2000

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    ora-00060 in the alert log.
    Deadlocks are recorded in trace files
    You must rollback the remainder of the transaction that casued the deadlock (the statement which detected it is automatically rollback by oracle)



    sorry for the plagerism!

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Oh and...

    since v$lock has SID, you may join V$LOCK with V$LOCKED_OBJECT and v$SESSION.


    SELECT a.sid, a.username, a.schemaname,a.sql_address, a.terminal, SUBSTR(c.OBJECT_NAME,1,20) object_name, b.sql_text
    FROM v$session a,
    v$sqlarea b,
    USER_OBJECTS c
    WHERE a.process in ( select distinct process from v$locked_object )
    AND a.sql_address = b.address
    AND c.OBJECT_ID IN ( SELECT OBJECT_ID FROM v$locked_object);



    -- I got this query form this forum (Plagerism again!!)
    -- lock.sql - Shows User Lock Information
    --
    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

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Run the catblock.sql script. This will be available under
    \rdbms\admin folder.

    After completion of this you can either run utllockt.sql (available on the same folder)
    or
    select * from dba_blockers ;(lists only the blocking session id)
    or
    select * from dba_waiters ;(lists all the waiting session, blocking session etc.)

    HTH.

    Thanks.
    Vijay.
    Say No To Plastics

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