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

Thread: How to find out username from tracefile information during a deadlock

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    How to find out username from tracefile information during a deadlock

    Hello all,

    There was a deadlock issue today morning,and from the trace file, we hvae the info about the 2 session id which were responsible for that.

    Is there any way to find out the Username also ?

    I want the info for the past deadlock, not for the present.

    Thanks

  2. #2
    Join Date
    Dec 2001
    Posts
    96
    This is the info from the trace file.

    *** 2003-08-18 08:07:40.590
    *** SESSION ID:(560.60728) 2003-08-18 08:07:40.560
    DEADLOCK DETECTED
    Current SQL statement for this session:
    SELECT S.AVAILABLE,GET_AVAIL(S.PRODUCT_ID),S.ON_HAND,S.W_PL,S.PRODUCT_GROUP,S.SIZE_ID,S.COLOR_ID,S.ONEZ,S.C OST,IS_IT_KIT(:b1),
    NVL(PR.R_AVAIL,0),NVL(PR.R_SOLD,0),NVL(PR.UNIT_OF_MEASURE,'QTY'),NVL(DECODE(PR.AV_CT,0,1,PR.AV_CT),1 ) FROM ACNTV.SKU S,ACNTV
    .PRERED_QUANTITIES PR WHERE S.PRODUCT_ID = PR.PRODUCT_ID (+) AND S.PRODUCT_ID = :b1 FOR UPDATE OF S.ON_HAND,
    S.W_PL,PR.R_AVAIL
    The following deadlock is not an ORACLE error. It is a
    deadlock due to user error in the design of an application
    or from issuing incorrect ad-hoc SQL. The following
    information may aid in determining the deadlock:
    Deadlock graph:
    ---------Blocker(s)-------- ---------Waiter(s)---------
    Resource Name process session holds waits process session holds waits
    TX-0021004b-000125d8 366 560 X 329 135 X
    TX-0008003b-000125fc 329 135 X 366 560 X
    session 560: DID 0001-016E-00000002 session 135: DID 0001-0149-00000002
    session 135: DID 0001-0149-00000002 session 560: DID 0001-016E-00000002
    Rows waited on:
    Session 135: obj - rowid = 00017FAC - AAAX+sAAPAAAS9/AAP
    Session 560: obj - rowid = 0001586C - AAAVhsABXAAAAIRAAN

    We know the objects ,but how to find out the username ?

    Thanks

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    You need a logon trigger that writes session information in some table.
    Then you could look back to that table see whos were sessions 135 and 560 at 2003-08-18 08:07:40.590.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Dec 2001
    Posts
    96
    Thanks TomazZ

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